How do I split single cell contents into multiple columns without seperators?

Question

I have an excel spreadsheet that has the following:

Cell A1 ABCD 1234

I would like to split this to be

CELL A1: ABCD

CELL B1: A  
CELL C1: B  
CELL D1: C 
CELL E1: D

I tried using the text to column tool but since there is no separators like spaces or commas I cant seem to split it up properly.

Thanks for the help.

Solution

There's 2 options:

First, assuming you have Excel 2007 or greater:

1. Select the cell you want to parse
2. Go to Data->Text To Columns
3. Choose Fixed width.
4. Add Break lines until it all lines up the way you want.
5. Finish

Second, you can do this entirely in code:

1. Put your target data in cell A1

2. In Cell B1, add =MID($A$1,COLUMN()-1,1).

3. Fill the formula to the right until you run out of characters.

   This formula takes 1 character at a time out of the target cell based on the column number (this formula would need work if you need to parse it to the same column as the target cell)

Does that help? If not, can you tell us more?

Comments

[fixer1234]

How would transposing split up a string?