 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
| |
Excel Formulas Manager Making working with Excel Formulas a breeze! See Also:Excel Formulas Here.Count Words in Excel ||Extract Words From Text .....
On This Page: || Excel Text Formulas | Excel Date Formulas | Excel Number Formulas
For Free 24/7 Microsoft Office � Support See:
Our Free Excel Help & VBA Help Forum
Excel Text Formulas
Here are a few examples of how you can use Excels Text Functions to extract parts of peoples names from a list. The same formulas could also be used on any text, they don't have to be names
Full Name | Formula Used | Result |
A2=David Hawley | =LEFT(A2,FIND(" ",A2)) | David |
A3=Ray Goodwin | =MID(A3,FIND(" ",A3,1)+1,LEN(A3)) | Goodwin |
A4=Graeme Dee | =LEFT(A4)&MID(A4,FIND(" ",A4)+1,1) | GD |
A5=Suzanne Greenhouse | =LEFT(A5)&MID(A5,FIND(" ",A5),LEN(A5)) | S Greenhouse |
A6=Fred Baker | =LEFT(A6,FIND(" ",A6))&MID(A6,FIND(" ",A6)+1,1) | FredB |
A7=Mary Hardwick | =MID(A7,FIND(" ",A7,1)+1,LEN(A7)) & " " & LEFT(A7,FIND(" ",A7)) | Hardwick Mary |
A8=Banana | =LEN(A8)-LEN(SUBSTITUTE(A8,"a","")) | 3 i.e occurence of "a" |
Tips:
How to copy Excel formulas without the reference changing.
This can be achieved by either pressing F2 and then highlighting the formula, Copy, Enter then paste to destination. Or doing the same in the Formula bar. However, this is not much good for large amounts of data, so try this: Select the range of cells with Formulae, use the Ctrl key for non-contiguous ranges. Now go to Edit>Replace and Replace = with #. Copy and paste to your location and then simply use Edit>Replace # with =
To copy and transpose formulas without the reference changing
In cell A1 of sheet 2 put: =Sheet1!A1 now copy this down a max of 255 rows. Now with the formulas selected go to Edit>Replace and Replace = with #. Now copy, select cell B1, go to Edit>Paste special and chooseTranspose. Delete Column "A" and with Row 1 selected go to Edit>Replace and Replace # with =
If you paste any formulas copied from a Wepage into an Excel cell, push F2 first. This will prevent any problems.
To make any of the Excel formula results upper case, nest the entire formula within the UPPER Function, eg; =UPPER(LEFT(A2,FIND(" ",A2)))
If the text you are working with is not properly capitalized (eg; david hawley) then nest the entire Excel formula within the PROPER Function, eg; =PROPER(LEFT(A2,FIND(" ",A2)))
If the text has not been spaced properly, use the Excel formula, TRIM eg; =TRIM(LEFT(A6,FIND(" ",A6))&MID(A6,FIND(" ",A6)+1,1))
If you only need to separate the first and last names you can use the Text to Columns feature. Before using this feature make sure the column immediately to the right of your data is empty:
Select the data you want to seperate.
Go to Data>Text to Columns...
Check the "Delimited" option button. Click"Next".
Select "Tab" or "Space". Click"Finish"
|| Excel Text Formulas | Excel Date Formulas | Excel Number Formulas ||
Date Excel Formulas
Working with dates in Excel is a common task and Excel has made the process relatively easy by supplying a good selection of Date Functions. Below is just a very small example of what you can do.
Number | Formula Used | Result |
A10=22122001 | =DATEVALUE(LEFT(A9,2)&"/"&MID(A9,3,2)&"/"&RIGHT(A9,4)) | =22/12/2001 |
A11=September | =CHOOSE(MATCH(A11,{"January";"February";"March";"April"; | 9 |
A12=22/05/01 | =DATEDIF(A13,A12,"M") ie; how many months between A12 & A13 | 16 |
A14=15/12/01 | =EOMONTH(A14,3) ie; the last day of the month 3 months from A14 | =31/03/2002 |
A15=18/02/01 | =EDATE(A15,-1) ie; the date 1 month before A15 | =18/01/2001 |
Imported Dates
SAP (and other Software) can import dates into Excel as 200011, which means the eleventh week of the year 2000.
How to find out which month that is? Below is one way to achieve a result. However, since Excel 2002 it is possible to use Text to Columns found underData on the Worksheet Menu Bar. At step 3 of 3 click the Advanced button. See Also:Convert Excel Dates and Excel Date and Times
What we need to do is first understand how Excel sees dates and that is as what's known as "Serial values". This is just a fancy name for whole numbers (times would add a decimal or fraction). Excels date system (by default) Start s from 1/Jan/1900 and this is stored by Excel as the Serial value 1, 2/Jan/1900 is 2 etc. You can see any dates "Serial value" by typing a valid date in any cell, then formatting the cell as "General".
We first need to convert the year 2000 into a serial number and to do that we type 1/jan/2000 into a cell and format it as "General". We get the serial value: 36526. Now we need to convert the week number (11 in this case) to days, this can easily be done by multiplying 11*7 to get 77. then we can add this to the year 2000 serial value (36526) and get 36603. If we now format this to a date format we get: 18/Mar/2000
To bring this all together we will need to always extract the week number from the SAP date (200011 in this case). and to do this we simply use: =RIGHT(A1,2) assuming the date is in cell A1. this will return the Week number 11, but as text! So this "Text value" must be converted to a true number, so we use the VALUE function: =VALUE(RIGHT(A1,2)). Obviously this will be fine when the week number is two digits, but if it's only one it wont work, to handle this we use a simple IF function and nest the LEN function into it's Logical_test argument and next the whole formula within the VALUE function. =VALUE(IF(LEN(A1)=6,RIGHT(A1,2),RIGHT(A1,1))). The LEN function simply returns the number of characters in text. So on the SAP date 200011 we could use:
=VALUE(IF(LEN(A1)=6,RIGHT(A1,2),RIGHT(A1,1)))*7+36526 and format the cell as a date.
Next problem will be the year changing from 2000 to 2001 etc. to handle this we would use:
=DATEVALUE("1/Jan/"&LEFT(A1,4)) this would convert the SAP date: 200011 to the Serial value: 36526. In other words it extracts the first 4 characters from the value 200011 (ie 2000) and this is used in a text date of "1/Jan/2000" the DATEVALUE function simply converts this text date to a serial value for us.
To bring the whole formula to a conclusion we would use:
=VALUE(IF(LEN(A1)=6,RIGHT(A1,2),RIGHT(A1,1)))*7+DATEVALUE("1/Jan/"&LEFT(A1,4))
Which gives us: 36603 (18/Mar/2001) in the case of 200011, 36969 (19/Mar/2001) in the case of: 200111
All you need to do is format the cell as "mmm" or use: =MONTH(C1) assuming your result is in cell C1
Tips:
If you paste any formulas copied from a Webpage into an Excel cell, push F2 first. This will prevent any problems.
The DATEDIF Function can also accept "Y", "D", "MD", "YM", "YD".The earliest date must be first. It is provided in Excel 97 onwards for compatibility with Lotus 1-2-3.
Both the EOMONTH and EDATE are Excel Add-ins. Go to Tools>Add-ins-Analysis Toolpak. If you use them without installing this Add-in you will get the #NAME? error.
You can use positive and negative numbers in theEOMONTH and EDATE functions.
See Also:Convert Excel Dates and Excel Date and Times
|| Text Formulas | Date Formulas | Number Formulas ||
Excel Number Formulas
When numbers are imported into Excel they may be converted to text, or not in a recognised Excel format. Below are some examples of how you can convert the numbers to suit.
Number | Formula Used | Result |
A18= 22- | =VALUE(RIGHT(A18,1)&LEFT(A18,FIND("-",A18)-1)) | -22 |
A19= -22 | =ABS(A19) | 22 |
A20= 1995 | =ROMAN(A20) | MCMXCV |
A21= 25.499 | =ROUND(A21,0) | 25 |
A22= 15.999 | =TRUNC(A22) | 15 |
Tips:
A quick way to convert text numbers to real numbers is to place the number 1 in any cell, copy it and then select your range containing the text numbers. Go to Edit>Paste special-Multiply and clickOK.
Another way to convert text numbers to real numbers is to use: =VALUE(A1) where A1 contains the text number. Copy this down/across as far as needed, then highlight all the formulas and copy, then go to Edit>Paste special-Values and click OK.
Have a lot of cells containing formulas that are showing the formula instead of the result? It is most likely because the cells are formatted as Text (possibly due to importing). Select the cells and format them as "General" (or any number format) then go to Edit>Replace and type = (equal sign) in the "Find what" box. Now type another = (equal sign) in the "Replace with" and click "Replace All". This will force all formula cells on the Worksheet to recalculate!
More on Excel Formulas here
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates