How to Manipulate TEXT and Clean Up Your Excel Data

# How to Manipulate TEXT and Clean Up Your Excel Data

**AUTHOR: JASON MORRELL **

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

## 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.

**Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! **

**ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.**

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