Last week my wife, who is an accountant also, asked me if there was a way to append or prepend a value in a cell. Yes there is.

Assume, for example, that you have a spreadsheet with a long listing (thousands of rows) of account numbers in column A. Further assume that each account number is currently nine digits long (i.e, 123456789) and, for whatever reason, you need to add a “00” in front or in back of the nine digit account number. So, the account number looks like this:

123456789

And, you want it to look like this:

00123456789 or 12345678900

Remember, you’re going to add “00” either to the beginning or the end of the nine digit account number for thousands upon thousands of rows, so you don’t want to do this manually. The quickest why to do this is to use the CONCATENATE function. The function looks something like this:

=CONCATENATE(text1,text2,...)

text1, text2,... are text or numbers that you want to combine together. The text/number will be combined in the order in which you list them. Our example will look like this:

The function in cell C1 is =CONCATENATE(A1,B1), and the result will be 12345678900. The function in cell C3 is =CONCATENATE(B3,A3) and the result will be 00123456789.

Even Shorter

CONCATENATE is a long function to type out. A shorter and faster way to concatenate values is to use the ampersand charger in place of CONCATENATE. The function will change slightly to: =A1&B1 or =B3&A3.

If you want it even shorter, and drop the use of column B altogether, you could type =A1&"00" or ="00"&A3.

Notice that the “00” is now in quotes. Using the ampersand to concatenate is extremely easy and it’s my preferred method for concatenating.