separate text and digits within a cell
 

I have some data like this:
BTG400, ADTD2430, AE50 etc.
I would like to separate the data in 2 columns with text in one column and
number in another column.
The max character for the text part is 4, and max of digit for the
numberpart is also 4.
Can someone help me please?
Thanks!

separate text and digits within a cell
 

Hi Ken,

[A1]="ADTD2430"

in cell [B1] put this formula
=LEFT(A1,LEN(A1)-LEN(C1))

in cell [C1] put this array formula (Ctrl+Shift+Enter):
=--MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),LEN(A1))

Regards,
KL


"Ken Leung" <Ken wrote in message
...
I have some data like this:
BTG400, ADTD2430, AE50 etc.
I would like to separate the data in 2 columns with text in one column and
number in another column.
The max character for the text part is 4, and max of digit for the
numberpart is also 4.
Can someone help me please?
Thanks!