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