Question: In order to analyze my worksheet and compare it with another, I need to split cells apart into separate columns. I have been trying to use if formulas, but can’t seem to get it to work.

Answer: I try to avoid complex formulas whenever possible, so the easiest way to do this, as long as there are consistent characters or spaces, is to use Excel’s text to columns feature.

1. Determine how many columns you will need (including the current cell) and insert them to the right of your cell that needs to split. If you need 3 columns for your data, you will need to add 2 columns.
2. Select the cells you want to split
3. Click the Data tab and select the Text to Columns button in the data tools group. The text to columns wizard appears.
4. Select delimited if you have consistent text. If you have a certain number of characters, you can select fixed width. Click Next.
5. If you selected delimited, choose the character to use to separate your text. (such as comma) If you don’t see the character, select Other and type in the character). If you selected Fixed Width, click the ruler where you want to separate text.
6. Assign data type to the new columns (if necessary)
7. Click finish.

In this example, they needed two digits, so to add zeros before single digits in the new columns. We used the Replace All (found under the Home Tab – Find/Select) to each single digit 1, 2, 3, etc. and replaced all with ’01 or ’02 or ’03, etc. That allowed the zero to display in a text field.