Working With Text in Excel – String Functions

More on the Excel String Functions

Excel has an array of string functions which are helpful in managing data. In this article we will look at how to extract strings from other strings:

So in the above we want to extract the middle word in each string e.g BLUE in the phrase “BIG BLUE SHIRT”. Once again we will go step by step . The key to this to find the position of the two spaces either side of the middle word.

The FIND function can be used to Find a position of a string within another string:

FIND(Search String, Source String, Starting Position)

So if we are looking for the space in the phrase “BIG BLUE SHIRT” (which is in cell A5) we enter the following in Cell C5:

C5 = FIND(“ “,A5,1)

And copying this down over all the strings we have:

Now we can use the position of first space to as an argument in another FIND function –

E5 = FIND(“ “,A5,C5+1)

Where we have added 1 to the value in the position of the first space so that we start at the next character:

The function MID allows us to extract a substring – providing we specify how many characters we want and the starting position:

G5 = MID(String, Starting Position, No of Characters)

So if we wanted to extract the 4-6th characters from the word “wonderful” -we would have:

MID(“Wonderful”,4,3)

So the now we have position of the first and second spaces we have:

G5 = MID(A5,C5+1,E5-C5-1)

Where we have added 1 to the position of the first space as we want the next character. The formulae E5-C5-1 ensures that we grab the relevant characters from the string:

Leave a Comment