Excel Text Functions

Published:

imageby Rich Wagoner, CITP.CPA | Principal, Director of Technology

How many times have you received a spreadsheet and one of the columns needs split up into separate cells or maybe you need to combine a few cells into one cell. Excel has a number of text functions that can help with these tasks and reduce your time fixing the spreadsheet.

Splitting Contents of One Cell

For example you export your trial balance to Excel but the 3 segment account number shows up in one column instead of 3. Here is a quick way to separate out the account segments. Let’s say the account number structure is 99-99999-99, your data starts at cell A1 and you want the account number split into three columns.

1. Insert four (4) columns after the account number column (Column B, C, D and E are not empty)

2. In Column B enter the following formula =Left(A1,2)

 

A

B

C

D

E

1 14-56897-588 =LEFT(A1,2) =RIGHT(A1,9) =LEFT(C1,5) =RIGHT(C1,3)

Results

1 14-56897-588 14     56897-588 56897 588

3. Copy the formulas down the page to the end of your account listing and then copy everything.

4. Next, Paste Special and then As Values (this will turn the formulas into the text that you need)

5. Delete the columns that are no longer needed (A and C)

Combining Multiple Cells

Let’s say you just exported your vendor list from your accounting system but the City, State and Zip all ended up in separate columns and you need it in one column. Here is a quick way you can combine them into one. Let’s assume your data starts in A1 for this example.

1. Insert one (1) column after the Zip code (Column D)

2. Enter the Formula “=A1&”, “&B1&” “&C1”Note: Any time you want to combine two items simply use the & sign and any time you want to add additional text not in a cell enclose that in quotes.

 

A

B

C

D

1 Indianapolis IN 46224 =A1&”, “&B1&” “&C1

Results

1 Indianapolis IN 46224 Indianapolis, IN 46224

3. Copy the formulas down the page to the end of your account listing and then copy it and paste a values (this will turn the formulas into the text that you need)

4. Delete the columns that are no longer needed (A thru C)