Seperating numbers from text

[mns]

Hi,
I am working on an excel file which has a column that contains product names
+ serial numbers. Dont know how but someone combined that data. they used to
be in different columns but now they are in same column and have no spaces
between them.
cells have names like this,
productname1345

is there any easy way to scan through the coulmn and at least seperate the
data like
productname 1345

after this step i think i can handle it

thanx for help.

 

[Mike H]

Hi,

With your string in A1 put this in C1 to extract the number
=LOOKUP(10^23,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

Put this in B1 for the name
=LEFT(A1,LEN(A1)-LEN(C1))

Mike

 

[Bob Phillips]

=LOOKUP(99^99,--("0"&MID(A10,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A10&"0123456789")),ROW(INDIRECT("1:"&LEN(A10))))))

and

=TRIM(SUBSTITUTE(A10,B10,""))