Split non delimited data into multiple cells
 

How do I split a single cell of data into multiple cells, when it is a
continous text string without delimiter characters? I want each single
charcter of the string in its own cell.

I am importing an ascii file and can separate the data manually during
importi, using the fixed width, delimiter function, but it makes me manually
place the seperator bar between each character. I could have up to 511
character text string so this is a bit tedious, and there doesn't seem to be
hot keys to do this, so a Macro doesn't seem possible.

Split non delimited data into multiple cells
 

Maybe something like this:

With text in A1 (eg abcde)

B1: =MID($A1,COLUMNS($B:B),1)

Copy that formula as far to the right as needed.
It separates each character into its own cell.
B1: a
C1: b
etc

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"KJM" wrote:

How do I split a single cell of data into multiple cells, when it is a
continous text string without delimiter characters? I want each single
charcter of the string in its own cell.

I am importing an ascii file and can separate the data manually during
importi, using the fixed width, delimiter function, but it makes me manually
place the seperator bar between each character. I could have up to 511
character text string so this is a bit tedious, and there doesn't seem to be
hot keys to do this, so a Macro doesn't seem possible.

Split non delimited data into multiple cells
 

Additional comment:
Since Excel has only 256 columns, that becomes an upper limit. Unless you're
using XL2007 or don't mind adjusting the formula to continue characters
beyond 256 elsewhere.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Maybe something like this:

With text in A1 (eg abcde)

B1: =MID($A1,COLUMNS($B:B),1)

Copy that formula as far to the right as needed.
It separates each character into its own cell.
B1: a
C1: b
etc

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"KJM" wrote:

How do I split a single cell of data into multiple cells, when it is a
continous text string without delimiter characters? I want each single
charcter of the string in its own cell.

I am importing an ascii file and can separate the data manually during
importi, using the fixed width, delimiter function, but it makes me manually
place the seperator bar between each character. I could have up to 511
character text string so this is a bit tedious, and there doesn't seem to be
hot keys to do this, so a Macro doesn't seem possible.

Split non delimited data into multiple cells
 

Hi Ron,

Yes I actually tried that since the post.

I used a column as a counter, and then used the mid statement in separate
rows, and incremented the starting point, using the indexing cell column.

That eliminated my column limitation.

The only problem I have still is that there are many Null's in the file I am
importing, and it looks like Excel ignores them. (imports 90 chacracters out
of a file with 511)

I might have to try a new post with a different subject if I can't figure it
out. (I get all 511 if cut and paste from Notepad to Excel)

Thanks for MID suggestion.
kevin

"Ron Coderre" wrote:

Additional comment:
Since Excel has only 256 columns, that becomes an upper limit. Unless you're
using XL2007 or don't mind adjusting the formula to continue characters
beyond 256 elsewhere.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Maybe something like this:

With text in A1 (eg abcde)

B1: =MID($A1,COLUMNS($B:B),1)

Copy that formula as far to the right as needed.
It separates each character into its own cell.
B1: a
C1: b
etc

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"KJM" wrote:

How do I split a single cell of data into multiple cells, when it is a
continous text string without delimiter characters? I want each single
charcter of the string in its own cell.

I am importing an ascii file and can separate the data manually during
importi, using the fixed width, delimiter function, but it makes me manually
place the seperator bar between each character. I could have up to 511
character text string so this is a bit tedious, and there doesn't seem to be
hot keys to do this, so a Macro doesn't seem possible.