Excel String Functions

Excel has an abundance of string functions that can be used to manipulate data. In this article we will look at how to convert somebody’s full name into their initial and surname. So for example Mark Doppler becomes M Doppler.

Lets say that we have a series of names like so:

It is possible to do this all in one LONG formulae but instead we will build it up – step by step.
The full name is entered in Column A. So we first need to find the person’s Initial. Excel provides us with a function called LEFT:

= LEFT (Cell Reference, Number of Characters)

So LEFT(B4,4) will return the 4 leftmost characters of value in cell B4. In this case we just need the first character of each string. For cell C4:

C4 = LEFT(A4,1) will return the H in Hunter

And then copying down over the entire range we have:

Now we need to “break” the name into its first and last name. This can be done by looking for the space character which separates out these two. The FIND function allows us to find the position of one string within another:

FIND(Search String, Source String, Starting Position)

So we need to find the position of the space in the name “Hunter Davies”:

E4 = FIND(“ “,A4,1)

Entering this formulae and copying down:

We now need the length of the name – which will help us to break the string. The function LEN gives the length of a string. E.g LEN(H22) will give the length of the string in Cell H22. So in cell G4 we type:

G4 =LEN(A4) so that we have the length of contents of cell A4 (our original name):

Just as Excel has the LEFT function, also has a RIGHT function – to the rightmost characters of a string. Now if we look at the difference in the last two columns e.g 7 and 13 we get 6. The 6th rightmost characters of the person’s name are its surname So we have the surname as:

I4 = RIGHT(A4,G4-E4) and then copying down:

So we’re almost there – we’ve worked out the Initial and the last name. Now we just have to combine them together. This can be done by using the CONCATENATE function – which just joins up all the strings:

K4 = CONCATENATE(C4,” “,I4) – as we need to join up the initial, then have a space and then the last name:

And we see how step by step we have gone from the full name to the desired result. This is a generally an approach that I recommend for Excel – small discrete steps rather than one big clunky formulae.

Courtesy: http://www.automateexcel.com/2008/08/23/excel-string-functions/