"How To Excel" Mini-Tutorials
by TheExcelAddict.com
"Helping Average Spreadsheet Users Become Local Spreadsheet Experts"

How To Combine Text in Cells

Here’s another little-known feature available in Excel that I bet you’ll find various uses for. It is the ability to combine text from multiple cells into one cell.

I use it frequently for joining text when I receive a file that has first and last names in separate columns. This feature allows me to easily combine the last name from one column with the first name of the other column and show the result as a full name in another column.

Let me show you how easy it is.

Before I get started, when talking about text I'll often use the terms ‘string of text’ or ‘text string’ to refer to text.

The best way for me to show you how this works is to give you a simple example to do yourself.

  • In cell A1 enter "First" (without the quotes)
  • In cell A2 enter "Mickey"
  • In cell A3 enter "Ozzy"
  • In cell A4 enter "Bart"
  • In cell B1 enter "Last"
  • In cell B2 enter "Mouse"
  • In cell B3 enter "Osbourne"
  • In cell B4 enter "Simpson"
Lets pretend that this is a spreadsheet with a list of 300 names and you need the full name in one cell. How would you go about doing this? It could take hours to retype hundreds of names.

Here’s the trick.

In cell C1 enter "Full Name" as your column heading.
In cell C2 enter either of the following formulas:

=A2 & " " & B2 (will give you "Mickey Mouse")
=B2 & ", " & A2 (will result in "Mouse, Mickey")

Now just copy the formula down for the remaining cells. At this point you'll want to convert the formulas in these cells into values. To accomplish this, just highlight all of the formulas in column C, click Edit, Copy, and then click Edit, Paste Special, Values, OK. Now, if you want, you can delete the original two columns of data.

NOTES: To insert a space between the first and last names we have added a space between two quotation marks. The ampersand (&) joins the text string from each cell into one string of text.

Why not print (CTRL+P) this tip and share it with your friends and associates?

Get more time-saving tips just like this one delivered to you by email every week in my FREE newsletter "Spreadsheet Tips From An Excel Addict". Subscribe at the www.TheExcelAddict.com/Newsletter.htm.
You'll be amazed how much time you'll save just by learning a few of these tips.

Copyright © 2003 All Rights Reserved by Francis Hayes (The Excel Addict)

If you came to this page from my Excel Mini-Tutorials page, click here to close this window,
otherwise click here and you'll find lots more time-saving Excel tutorials like this one.