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

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

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

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