On larger spreadsheets, it is important and necessary to be able to manipulate text, especially when you import it from another system and the formatting is all over the shop.
In this post I give you 9 text functions that hand you complete control over your data.
FEATURE DOWNLOAD
Grab the free Text Functions cheat sheet
1. Why are text functions so useful?
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.
2. Setting The Correct Case
- Text functions used: UPPER, LOWER, PROPER
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
=UPPER(A1)
To switch it back to lower case, type
=LOWER(A1)
To convert the name to Title Case (where only the first letter of each word – in this case, each name – is capitalised), type
=PROPER(A1)
3. Concatenating (joining) data
- Text functions used: CONCATENATE
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:
=A1 & " " & A2
Method 2:
=CONCATENATE(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))
or
=PROPER(CONCATENATE(A1," ",A2))
4. Removing leading and trailing spaces
- Text functions used: TRIM
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 …
=TRIM(A1)
removes the spaces to leave ‘Joe Bloggs’.
5. Separating a full name into first name and last name
- Text functions used: LEFT, RIGHT, SEARCH, LEN
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 …
=LEFT(A1,5)
extracts the 5 leftmost characters from the text in cell A1, and
=RIGHT(A1,10)
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)
returns 4.
The space between the names is in position 4.
Therefore …
=LEFT(A1, SEARCH(" ", A1)-1)
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
=LEN(A1)
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:
=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.
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.
- Alternatively …
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.
6. What next?
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.
I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.
Have a fantastic day.
About the author
Jason Morrell
Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.
He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.
Purely for amusement he sometimes talks about himself in the third person.
SHARE