Text Calculations in Excel

February 18, 2005

Most people think that Excel is just for calculating numbers. It also has a great variety of calculations and functions for managing text as well. Here is an Excel spreadsheet with first name in column A and last name in column B.

Say that you need First name and last name in a single column. One approach is to just start re-typing all of the information in column C. This would work, but would take a long time. There is a better way.

The operator to join text together in Excel is the ampersand character. This is called the concatenation operator.

In cell C2, enter the formula of =A2&B2.

This is close – but it gives a name of FRANKANSTEY instead of FRANK ANSTEY. Edit the formula to be =A2&" "&B2. Double click the fill handle to copy the formula down.

Excel has a variety of other functions for text. If you want the text to be in upper and lower case, edit the formula to use =PROPER(A2&" "&B2) to change the case. Other Excel functions include =LOWER() and =UPPER() to convert to lower or upper case.

Important!

You might be tempted at this point to delete columns A&B. If you do this, the formulas in column C will change to REF! errors.

Instead, select the data in column C and use Edit – Copy, then Edit – Paste Special Values to convert the formulas to values.

You can now safely delete columns A & B.