Using Excel to Generate Text

Posted on August 26, 2011 by
Categories: Software
Estimated reading time: 3 minutes, 17 seconds

Fridays are tip days at SimpleProductivity blog.


In the course of my job as a web programmer, I use Excel a lot. Not in the ways that most people use it, for graphs and calculations and nice neat rows of numbers.

No, Excel is my powerhouse for assembling text.

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, 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. Zip codes can be a bear. They are numbers that are treated as text, but sometimes Excel doesn’t seem them that way. You can fix this with some padding. We use the REPT function to generate a string of 0s, 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.

The Results

Below you can see the formulas and the results:



Did you know you can get Twitter and Facebook updates from this blog? For Twitter, look for @SmplProdBlog. On Facebook it’s Facebook.com/SimpleProductivity. You can also find me at Google+!


If you enjoyed this post, please buy me a cup of coffee!

Comments (3)

 

  1. #SimpleProductivity: Using Excel to Generate Text: In the course of my job as a web programmer… http://t.co/NTkvaxh via @SmplProdBlog