Split numbers and text from a cell
 

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?

Split numbers and text from a cell
 

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"))

--
Regards
Frank Kabel
Frankfurt, Germany

"Stefan" schrieb im Newsbeitrag
...
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?

Split numbers and text from a cell
 

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


"Frank Kabel" wrote in message
...
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"))

--
Regards
Frank Kabel
Frankfurt, Germany

"Stefan" schrieb im Newsbeitrag
...
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?

Split numbers and text from a cell
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

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


"Frank Kabel" wrote in message
...
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"))

--
Regards
Frank Kabel
Frankfurt, Germany

"Stefan" schrieb im Newsbeitrag
...
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?

Split numbers and text from a cell
 

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" wrote in message
...
Hi
have you defined the seq name (menu 'Insert - Name - Define')?

--
Regards
Frank Kabel
Frankfurt, Germany

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


"Frank Kabel" wrote in message
...
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"))

--
Regards
Frank Kabel
Frankfurt, Germany

"Stefan" schrieb im Newsbeitrag
...
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?

Split numbers and text from a cell
 

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

--
Regards
Frank Kabel
Frankfurt, Germany

"Stefan" schrieb im Newsbeitrag
...
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" wrote in message
...
Hi
have you defined the seq name (menu 'Insert - Name - Define')?

--
Regards
Frank Kabel
Frankfurt, Germany

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


"Frank Kabel" wrote in message
...
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"))

--
Regards
Frank Kabel
Frankfurt, Germany

"Stefan" schrieb im Newsbeitrag
...
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?

Split numbers and text from a cell
 

On Sun, 24 Oct 2004 12:56:11 +0200, "Stefan" wrote:

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

Split numbers and text from a cell
 

Another one...

=REPLACE(A1,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")-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.


Stefan Wrote:
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?


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=271891