Get my absolute best Excel tips, plus this

FREE CHEAT SHEET

Text Functions in Excel

Your details will be kept safe and will not be shared with anyone (here's the privacy policy)

In this post I give you 9 text functions that hand you complete control over your data.

Quick Navigation

**FEATURE DOWNLOAD**

Grab the free **Text Functions **cheat sheet

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:

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 Column**s 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.

If this post helped, or you have a question, drop a quick comment below. I always love to hear from my readers. Here's to your learning and success. Enjoy the rest of your day.

About the author

Jason Morrell

Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!

SHARE

**Session expired**

Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.