Splitting Information in a Cell

M

Mad Dog

How would I take this value in a single cell:

12345AB

and create this value in two cells, as follows:

12345 AB

Thanking you in advance!

Mad Dog
 
D

Dave Peterson

Is it always 5 characters followed by 2?

Data|Text to columns
or
you can use formulas
=left(a1,5)
=right(a1,2)
 
M

Mad Dog

Not always - but it is always numbers/txt. Sometimes the text part is not
there.
 
D

Dave Peterson

Put this in B1:

=--LEFT(A1,MIN(MATCH(TRUE,
ISERROR(-MID(A1&"x",ROW(INDIRECT("1:"&LEN(A1)+1)),1)),0))-1)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

If you want the stuff in column B to be text, then drop the -- at the beginning
of the formula (the -- converts the text to numbers.

and this in C1:
=MID(A1,LEN(B1)+1,255)
 
Top