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 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"