
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 noncontiguous 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 choose Transpose. 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 under
Data 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 123.
Both the EOMONTH and EDATE are Excel Addins. Go to Tools>AddinsAnalysis Toolpak. If you use them without installing this Addin you will get the #NAME? error.
You can use positive and negative numbers in the EOMONTH 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 specialMultiply and click OK.
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 specialValues 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 Addins Collection on all purchases totaling over $64.00. ALL purchases 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 Package Technical 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 & Addins Bundle  CodeVBA  SmartVBA  PrintVBA  Excel Data Manipulation & Analysis  Convert MS Office Applications To......  Analyzer Excel  Downloader Excel
 MSSQL Migration
Toolkit 
Monte Carlo Addin 
Excel
Costing Templates