can someone translate this formula pls

[nebrass]

I used this formula to seperate numbers in a string and return the numbers.
Example:
original string "BD456". the outcome is "456"

The formula worked just fine, but would someone please explain to me how it did that. The functions used don't follow the standard format that the stupid excel helper suggests; therefore, I didn't understand them. The function for a string in cell G2 is

=MID(G2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},G2&"0123456789")),1024)


Thank you in advance.
Mahmoud

[Bob Phillips]

=MID(G2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},G2&"0123456789")),1024)


SEARCH({0,1,2,3,4,5,6,7,8,9},G2&"0123456789")) will return an array of
{7,4,5,6,11,12,13,14,15,16} - note that these numbers relate to the position
in G2, but some are greater than 6 because we appended 0123456789

MIN will the return 4 - self explanatory

MID(A1, SEARCH(...),ROW(...),1024) then returns 1024 characters starting at
character 4, but as there are only 3 more, that is what you get, 123.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"nebrass" <[email protected]> wrote in
message news:[email protected]...
>
> I used this formula to seperate numbers in a string and return the
> numbers.
> Example:
> original string "BD456". the outcome is "456"
>
> The formula worked just fine, but would someone please explain to me
> how it did that. The functions used don't follow the standard format
> that the stupid excel helper suggests; therefore, I didn't understand
> them. The function for a string in cell G2 is
>
> =MID(G2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},G2&"0123456789")),1024)
>
>
> Thank you in advance.
> Mahmoud
>
>
> --
> nebrass
> ------------------------------------------------------------------------
> nebrass's Profile:
http://www.excelforum.com/member.php...o&userid=32802
> View this thread: http://www.excelforum.com/showthread...hreadid=532211
>

[Bob Phillips]

Note, if G2 holds ABX123XYZ, you will get 123XYZ.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"nebrass" <[email protected]> wrote in
message news:[email protected]...
>
> I used this formula to seperate numbers in a string and return the
> numbers.
> Example:
> original string "BD456". the outcome is "456"
>
> The formula worked just fine, but would someone please explain to me
> how it did that. The functions used don't follow the standard format
> that the stupid excel helper suggests; therefore, I didn't understand
> them. The function for a string in cell G2 is
>
> =MID(G2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},G2&"0123456789")),1024)
>
>
> Thank you in advance.
> Mahmoud
>
>
> --
> nebrass
> ------------------------------------------------------------------------
> nebrass's Profile:
http://www.excelforum.com/member.php...o&userid=32802
> View this thread: http://www.excelforum.com/showthread...hreadid=532211
>