seperate the character and number in formula
 

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

seperate the character and number in formula
 

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(FI ND(
{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
_ _
^¿^
--

seperate the character and number in formula
 

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Soo Cheon Jheong" <exceler@korea wrote in message ...
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(FI ND(
{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
_ _
^¿^
--