How to Manipulate TEXT and Clean Up Your Excel Data

To switch it back to lower case, type

To convert the name to Title Case (where only the first letter of each word – in this case, each name – is capitalised), type

If you had ‘joe’ in cell A1 and ‘BLOGGS’ in cell A2, you can join them together using concatenation. There are 2 ways to do this:

Method 1:

Method 2:

With the first method, a concatenation character (‘&’) is used to connect one item to the next.

With the second method, the CONCATENATE function is used, and all the items to be joined together are listed inside the brackets.

Notice that with both methods, the 2 names are connected with a space to separate them.

You could also fix up the case errors by combining PROPER with either method, such as

or

Often, when you import data from systems like SAP, a lot of the data has to be manually fixed before it can be used by Excel.

A common occurrence is leading and training spaces which affect everything from text length to no matches being found when using functions like VLOOKUP.

Leading spaces can often be seen, whereas training spaces often cannot be seen. Either way, manually fixing them is NOT the way to go.

Instead, a trim function removes any leading or trailing spaces on cell content. But don’t worry, spaces between words are not removed.

If cell A1 contained <space>Joe Bloggs<space> then …

removes the spaces to leave ‘Joe Bloggs’.

The LEFT and RIGHT functions extract a set number of characters from the left or right end of a string (the technical name for a block of text). So …

extracts the 5 leftmost characters from the text in cell A1, and

extracts the 10 rightmost characters from the text in cell A1.

However, if you want a formula to work for any name, we need to employ a slightly more sophisticated technique to calculate how many characters to extract.

I am going to use 2 additional functions – SEARCH and LEN.

SEARCH finds the position of string A within string B, so …

returns the position of the first space within the text in cell A1. Once we know this we can subtract 1 to calculate the number of characters to extract from the left side of a ‘full name’ call.

For example, if cell A1 contains ‘Joe Bloggs”, then

returns 4. 

The space between the names is in position 4.

Therefore …

extracts ‘Joe’.

The reason the formula subtracts 1 is because the position of the space is 4, therefore the number of characters to extract is 3.

To obtain the surname, we need to also use LEN. The LEN function calculates the total length of a string, so if we continue to use our example where cell A1 contains ‘Joe Bloggs’, then

returns 10.

If we subtract the position of the space (4) from the total length of the text (10) then we have the length of the surname (6), which is the number of characters we need to extract using the RIGHT function.

Here is the complete formula to extract the surname from a full name:

If you want to fix up any case issues at the same time, then wrap the whole formula above inside a PROPER function, like this:

Here’s how it works.

The SEARCH finds the position of the space (char 4), then subtracts this from the length of the whole name (10 chars) for an answer of 6. It then extracts the 6 right most characters from the full name, before using the PROPER function to apply the correct capitalisation.

Here’s how it works.

The SEARCH finds the position of the space (char 4), then subtracts this from the length of the whole name (10 chars) for an answer of 6. It then extracts the 6 right most characters from the full name, before using the PROPER function to apply the correct capitalisation.

I can hear some of you jumping up and down shouting “What about Text To Columns!?” so I wanted to address that quickly.

The Text To Columns feature (on the Data tab) allows you to take a large block of data, normally imported, and break it into separate columns, either based on a fixed length of text or using a separator like a space, tab character or hyphen.

You’re right! In this scenario, the Text to Columns feature is the best tool to use. It's easy and quick. However, make sure you trial it on a small sample size first before committing completely.

You might also want to try Flash Fill - introduced in Excel 2013. It's super easy but has some limitations.

The functions I’ve discussed in this post allow you to make up your own rules and they give you a much finer element of control.

This is just a sample of the text function that Excel offers you. To see a complete list, click the Formulas tab, then click the TEXT FUNCTIONS icon.