Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Media
Contact
Home

 

Text Calculations in Excel


Amber MacArthur, Bill Jelen, Leo LaPorte, Andy Walker on the set of Call for Help on TechTV Canada.

As mentioned on the show, you can get a free chapter a week from the new book by signing up here.


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.

For the BEST TV show on technology, check out Call for Help.

This tip was originally published on February 18, 2005 and aired on TechTV on March 23, 2005. The permanent URL for this page is http://www.mrexcel.com/tip088.shtml.

If you are looking for show notes from another episode, visit my complete list of TechTV appearances.

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

 

Excel is a registered trademark of the Microsoft® Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

All contents Copyright 1998-2008 by MrExcel Consulting.