Working with Text
Home Up

 

 

 

Manipulating Text

 

Assume you have the entries in column A below.  How do you break the full name into first and last?  The answer is with text functions: LEFT, RIGHT, LEN AND SEARCH.  Note: these are not the only text functions available in xl see help for others.  The examples below refer to the xl worksheet following

 

Left will begin at the left edge of a piece of text and returns a specified number of characters

 

LEFT(A2,10) = FLINTSTONE

 

Right will begin at the right edge of a piece of text and returns a specified number of characters

 

RIGHT(A2,4) = FRED

 

This is fine BUT by the time you count all the characters in each cell you could just as easily retype the names.  This is NOT necessary.

 

Len returns the length of a piece of text

 

LEN(A2) = 16

 

Search will look for a specific character or string within a large piece of text.  It returns the position of the character(s)

 

SEARCH(",",A2,1) = 11

 

From these two functions we know that the text in A2 is 16 characters long and the comma is in the 11th position. Therefore, if we replace the number 10 in the left formula above with the search function

 

LEFT(A2,(SEARCH(",",A2,1)-1)) = FLINTSTONE

 

Note the –1 appended to the search function.  We don’t want the comma just the letters.  Similarly

 

RIGHT(A2,LEN(A2)-(SEARCH(",",A2,1)+1)) = FRED

 

The length of the text minus the number of characters where the comma is located  plus 1 more for the space between the comma and the first name.

Enter the left function above in column B and the right function in column c and fill down to break the full name apart.

 

If on the other hand you had last and first name as below in column B and C you can create the entries in column A with the following formula

 

=B2&”, ”&C2