Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 _ _ ^¿^ -- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 _ _ ^¿^ -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seperate text and number to different cells | Excel Discussion (Misc queries) | |||
seperate street name and number | Excel Worksheet Functions | |||
How do I seperate a 10 digit number into one cell for each number? | Excel Discussion (Misc queries) | |||
remove cr character from a + number and change number to - 159cr | Excel Worksheet Functions | |||
seperate number and character in a cell | Excel Programming |