Using the CONCATENATE, LEFT, RIGHT and MID functions in Excel 2007
Hi, and welcome to my latest hub on joining or splitting text using Excel 2007. Names or other text do not always come into your workbooks in exactly the format you want them to, so this hub will introduce you to some of the functions Excel 2007 has built in to help you get your data formatted exactly how you want it to be formatted. You can separate names, addresses or in fact any text you desire in almost any way you can think of. This hub will take you through a number of examples to illustrate what is possible in Excel 2007.
Suppose for example, you have a cell that contains John Smith and you want to split it into two cells, one containing John and the second containing Smith. Using the LEFT and RIGHT functions, you can create a formula in Excel 2007 to accomplish this.
If you have two cells containing a person’s first name in one cell and second name in a second cell and you want to combine them into one cell, you can use the CONCATENATE function to do this.
Splitting text using the Convert Text to Columns Wizard
Before we delve into functions, there is another way to split data in Excel 2007. It uses the Text to Columns button in the Data Tools group on the Data tab.
To begin, select the text you wish to separate and then click on the Text to Columns button
Select Delimited as the file type that best describes your data as below
On the next screen, you need to decide which delimiters you wish to use. In my example, my data is separated by spaces, but you can use any character you wish as a delimiter.
If you have multiple spaces (delimiters) in your cells, select Treat consecutive delimiters as one
As you see from the screenshot below, in the Data Preview tab, Excel 2007 shows you how the data will look once you click Finish
Unless you are unhappy with the preview, clickFinish
Excel will now have spilt your data into adjacent columns based on your selected delimiter
Splitting text using the LEFT and RIGHT functions
Splitting text can also be accomplished using the LEFT and RIGHT functions in Excel 2007. These are not limited to using delimiters to split text; they can split off a specific number of characters or can search for a specific character and split everything before that character.
Suppose I have a cell containing
I want Excel 2007 to take the first five characters in that cell and put them in another cell.
If I use the following formula,
The result of that formula will be
In my example, I want to use Excel 2007 to give me the first name of the name contained in a cell. The names are separated by spaces as you can see from the picture below. Using the LEFT function in the formula =LEFT(J15,SEARCH(" ",J15)) provides me with the correct result (in this case Bill).
To obtain the surname, I use the RIGHT function. This time, I want Excel 2007 to give me everything to the right of the space (in other words the surname). The formula for this is
=RIGHT(J15,LEN(J15)-SEARCH(" ",J15)) as illustrated in the picture below.
Now suppose I had a customer who had a middle initial and I wanted to split them into their own cells, so that I had first name, initial and surname all separated into columns. The LEFT and RIGHT functions in this case would not work, so I use a third function called MID
This formula looks rather frightening, but Excel 2007 is just using the two spaces in the cell to calculate the number of characters between them to extract and then extracting them. The formula is shown below.
Within the formula, it is worth pointing out that there are spaces before and after the initial that is extracted from the name which will make life much easier should you want to use the middle initial for another purpose. The final +1 in the formula (bolded below) controls adding a space after the initial.
=MID(J15,SEARCH(" ",J15,1),SEARCH(" ",J15,SEARCH(" ",J15,1)+1)-SEARCH(" ",J15,1)+1)
NOTE: as the formula looking for spaces to separate out the middle initial(s) from the first and last name, should someone have three middle initials, as long as they are not separated using spaces, the formula will still produce the correct result.
Joining together text
My favourite function when working with text is CONCATENATE. In my music store, I like to send emails to customers thanking them for their custom. I would like to send them a reasonably personalized but automatically created email based on their order details in my Excel 2007 workbook. CONCATENATE can help me achieve that.
Below I have my order book
First I extract the first name from the customer name using =LEFT(Q2,SEARCH(" ",Q2)). I then create my email using CONCATENATE. Using the following formula
This gives me the following output:
Hi Bill, Thanks for your recent purchase of Bat out of Hell by Meatloaf
I can then paste that into an email template I have already created and send it off. I can also re-use this formula to automatically create this text for all of my customers in my order book.
Using the functions outlined in this hub, you can easily extract first names, surnames, middle initials or any other text from cells you desire using the LEFT, RIGHT and MID functions in Excel 2007. You can also join cells or text together using the CONCATENATE function. I hope that my examples used in this hub have given you some ideas as to how you can take these functions and use them in your day to day work with Excel 2007. Please feel free to leave any comments you wish in the comments box below, thanks again for reading!
I also have a number of other hubs on aspects of Excel 2007, covering everything from Conditional Formatting to creating charts and graphs. I have an Index hub which also covers how I successfully transitioned from Excel 2003 to 2007 as well as outlining my other Excel 2007 hubs which can be found here