Generate Text With Excel

Microsoft Excel, which is a spreadsheet meant for crunching numbers, may be the tool of accountants. But I rely on it heavily to help me with text. I generate text using Excel in order to feed data cleanly into systems.

It occurred to me that people might get some benefit from learning how to do this, so here is my method:

The Data

Usually when I am assembling chunks of text, the data has come to me with bits of it in columns, and I may have to add in other text. Since I don’t want to geek out on you here, I’ll use an example with putting together addresses.

Let’s say we have a spreadsheet with 6 columns: First Name, Last Name, Address, City, State and ZIP. We want to transform those into three columns: Name, Address, CityStateZIP. Here is what the data would look like to start with:

Basic Text Formulas and Operators

There are a few things you need to know about working with text. Here is the summary:

All formulas start with “=”. The equals sign tells Excel it is going to do a “calculation”, even if it is on text.

Reference cells by their location. Excel has columns that are labeled with letters and rows that are labeled with numbers. To reference a particular cell (or “box”), use the column letter followed by the row number.

Know that Excel uses Relative Referencing by default. Excel formulas reference things relative to the position from where you are. So if you are in column G, row 2 and you reference column A, row 1, Excel sees this as “go to the left of where I am 5 columns and up 1 row”. Be careful with this, or it can lead to unexpected results.

All text that doesn’t change goes in double quotes. If you want to add something that doesn’t change into a text string, you put it in double quotes (“). For example, if you wanted the text to start out saying “The Grand High Exalted Muckity Muck”, the formula would be

="The Grand High Exalted Muckity Muck".

Concatenate with &. To put two chunks of text together in Excel, you need to use concatenation, which is the ampersand (&). The plus sign (+) is for adding numbers.

Example: to put John and Doe together, without a space in between, it would become

="John" & "Doe"

Trim off the spaces. Nothing can throw off putting text together than extra spaces. Trim off the extra spaces in your text by using TRIM(). If you wanted to format the name in the example above, the formula would become as follows. This translates to “trim the spaces off either end of the text in cell A2, then add a blank space, then add the text of B2, which has also had the spaces removed from both ends.

=TRIM(A2) & " " & TRIM(B2)

Pad ZIP codes with 0s. ZI{codes can be a bear. They are numbers that are treated as text, but sometimes Excel doesn’t see them that way. You can fix this with some padding. We use the REPT function to generate a string of zeroes, then add the ZIP code to the right of this, and take the rightmost five characters. (It sounds more complicated than it is).

=RIGHT(REPT("0",5) & F2,5)

You can add new lines to the text. If you need the text to appear on multiple lines (say for address labels) you can do that by inserting a CHR(10).

=C2 & CHR(10) & D2

Extend the formula with copying. To get the formula where you need it to be, copy the cell with your original formula into the cells that need it.