FREE CHEAT SHEET
All the key points in one handy PDF
Plus, collect a FREE copy of our 30 Excel Power Tips
ENTER YOUR DETAILS FOR IMMEDIATE ACCESS
Today I’m going to give you 9 text functions that hand you complete control over your data.
Have you ever downloaded a report or some stats from the web and found that the data is a mess?
In particular, people often enter names, addresses and place names all in lower case or all in upper case (capital letters) - LIKE THEY’RE SHOUTING AT YOU.
So what can you do?
Well, there is a whole category of TEXT functions in Excel that manipulate text.
Let’s start with 3 of the simpler text functions.
Say you have the name ‘joe bloggs’ in cell A1.
To convert cell A1 to uppercase, type
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:
=A1 & " " & A2
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
=CONCATENATE(PROPER(A1)," ", PROPER(A2))
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 …
=SEARCH(" ", A1)
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
=SEARCH(" ", A1)
=LEFT(A1, SEARCH(" ", A1)-1)
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
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:
=RIGHT(LEN(A1) – SEARCH(" ", A1))
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:
=PROPER(RIGHT(LEN(A1) – SEARCH(" ", A1)))
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 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. You will see the text functions discussed above plus many more.
Please share your thoughts and feedback in the comments below, and if you found this post useful, please share a link on your social networks.
Cheers. Here's to your learning and success. Enjoy the rest of your day.
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.