Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract numbers from a code
Is it possible to extract by formula numbers from a code, for example
Code Number AS12345WQ 12345 F87654321LD 87654321 HN123 123 I want to be able to take just the numbers from the code and place them in the column next to it I've tried playing around with Right, Left and Len but the problem i'm having is there could be any number of letters at the begining or end of the code thanks Mark |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract numbers from a code
Dear Mark
With your data in Col A try the below formula. Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" A1 = AS12345WQ In B1 (All in one line) =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),COUNT(1*MID(MID(A1,MIN(SEARCH({0,1,2,3,4, 5,6,7,8,9},A1&"0123456789")),99),ROW($1:$9),1))) If this post helps click Yes --------------- Jacob Skaria "Mark" wrote: Is it possible to extract by formula numbers from a code, for example Code Number AS12345WQ 12345 F87654321LD 87654321 HN123 123 I want to be able to take just the numbers from the code and place them in the column next to it I've tried playing around with Right, Left and Len but the problem i'm having is there could be any number of letters at the begining or end of the code thanks Mark |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract numbers from a code
Posted previously by Lars-Ã…ke Aspelin...
=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10 ^(300-ROW($1:$300))),2,300) This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. It has the following (known) limitations: - The input string in cell A1 must be shorter than 300 characters - There must be at most 14 digits in the input string. (Following digits will be shown as zeroes.) Maybe of no practical use, but it will also handle the following two cases correctly: - a "0" as the first digit in the input will be shown correctly in the output - an input without any digits at all will give the empty string as output (rather than 0). -- Rick (MVP - Excel) "Mark" wrote in message ... Is it possible to extract by formula numbers from a code, for example Code Number AS12345WQ 12345 F87654321LD 87654321 HN123 123 I want to be able to take just the numbers from the code and place them in the column next to it I've tried playing around with Right, Left and Len but the problem i'm having is there could be any number of letters at the begining or end of the code thanks Mark |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract numbers from a code
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))
"Mark" wrote: Is it possible to extract by formula numbers from a code, for example Code Number AS12345WQ 12345 F87654321LD 87654321 HN123 123 I want to be able to take just the numbers from the code and place them in the column next to it I've tried playing around with Right, Left and Len but the problem i'm having is there could be any number of letters at the begining or end of the code thanks Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to extract zip code from a 1 cell adress line? | Excel Discussion (Misc queries) | |||
vba code for excel to extract data from txt file | New Users to Excel | |||
how to extract numbers from imported cell with text and numbers? | Excel Discussion (Misc queries) | |||
how to extract unique numbers once from a list of repeated numbers? | Excel Discussion (Misc queries) | |||
Code Post: Extract Trendline coefficients | Excel Discussion (Misc queries) |