Split numbers and text from a cell

[Stefan]

Hi,

I have a column with cells containing codes formatted:

XXXXnnnnnn (where X=letter and n= number)
the number of letters is variable.

I want to split the cells in one column with text and one clomn with
numbers.
Can anyone help me?

 

[Frank Kabel]

Hi
try if the numeric part is always at the end:
B1: Text part
=LEFT(A1,LOOKUP(2,1/MID(A1,1024-seq,1),1024-seq)-1)

C1: numeric part:
= --MID(A1, LOOKUP(2,1/MID(A1,1024-seq,1),1024-seq),20)

where seq is a defined name with the formula
=ROW(INDIRECT("1:1024"))

 

[Stefan]

I tried this suggestion, but there seems to be an error in it, I don't get
it where the problem is.

 

[Frank Kabel]

Hi
have you defined the seq name (menu 'Insert - Name - Define')?

 

[Stefan]

Yes, I did, when I enter =seq in a cell, it returns the value 1, so the
defined name seq is active.

Regards,

Stefan

 

[Frank Kabel]

Hi
just tested the formulas again and they work. What error do you get
exactly and what is your value in the reffered cell?

 

[Ron Rosenfeld]

Hi,

I have a column with cells containing codes formatted:

XXXXnnnnnn (where X=letter and n= number)
the number of letters is variable.

I want to split the cells in one column with text and one clomn with
numbers.
Can anyone help me?

One way with *array* formulas:

(To enter an *array* formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula).

Letters:

=LEFT($A1,MATCH(TRUE,ISNUMBER(--MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)),0)-1)

Numbers:

=MID($A1,MATCH(TRUE,ISNUMBER(--MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)),0),255)

(Feel free to change the reference style of the range reference to your
requirements).


--ron

 

[Aladin Akyurek]

Another one...

=REPLACE(A1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")-1),"")

which must be confirmed with control+shift+enter instead of just with
enter.

This yields the numeric part of the string in text format. The result
can be coerced into a true number by double negating the formula, that
is, =--REPLACE(...)

The non-digit part can be obtained by:

=SUBSTITUTE(A1,B1,"")

where B1 houses the REPLACE formula.