seperate the character and number in formula

[tom taol]

A1 : dfg56Yt -> B1 : dfgYt -> C1 : 56
A2 : 549pp -> B2 : pp -> C2 : 549

I want to make above.
using only excel embedded function, not UDF.
namely in column B, only character
namely in column C, only number

 

[Soo Cheon Jheong]

Hi,

Enter a formula in B1:

=SUBSTITUTE(A1,C1,)

Enter a formula in C1:

=MID(A1,MIN(IF(ISERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1)),"",
FIND({1,2,3,4,5,6,7,8,9,0},A1))),MAX(IF(ISERROR(FIND(
{1,2,3,4,5,6,7,8,9,0},A1)),"",FIND({1,2,3,4,5,6,7,8,9,0},
A1)))-MIN(IF(ISERROR(FIND({1,2,3,4,5,6,7,8,9,0},A1)),"",
FIND({1,2,3,4,5,6,7,8,9,0},A1)))+1)

Then drag and fill down.


--
Regards,
Soo Cheon Jheong
_ _
^¿^
--

 

[David McRitchie]

Doesn't work for aa11bb22 but then there wasn't
a proper definition of the data to be used. If there is
only one contiguous area of digits then it works fine.

In any case the poster wanted digits in one cell and
letters in another cell, and there were two sets of
letters in the example.

When things get complicated I don't have the patience
to code a worksheet function instead of a user defined function.
Some UDFs using Regular Expressions which can be adapted
can be found in
Extraction of a Group of Digits and Dashes
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm
from postings by Harlan Grove