In a cell there is text and numbers, example: ABC123. In an other cell I want
to show the numbers only (123).

I can't find a useful function, any suggestions?

Hi Thomas

If your data is fixed at aaannn then you can use:

=RIGHT(A1,3)

Regards

Steve

Hi, if it's variable format without any seperating character like . or - then
it'll be extremely difficult without resorting to VBA.

A fixed format of 3 letters and then variable numbers it's is easier.
= right(A1,len(A1)-3)
3 letters and then 3 numbers even simpler:
=right(A1,3).

Post up if this helps or if you need a macro.

HTH

Giz

"ThomasVike" wrote:

In a cell there is text and numbers, example: ABC123. In an other cell I want
to show the numbers only (123).

I can't find a useful function, any suggestions?

Regardless of position

=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Scoops" wrote in message
oups.com...
Hi Thomas

If your data is fixed at aaannn then you can use:

=RIGHT(A1,3)

Regards

Steve

Hi Bob,

I take it back about needing a macro if variable :-o

Can you explain the elements in this please? With the 9.9... and {} etc I'm
lost.
Cheers

Giz

"Bob Phillips" wrote:

Regardless of position

=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Scoops" wrote in message
oups.com...
Hi Thomas

If your data is fixed at aaannn then you can use:

=RIGHT(A1,3)

Regards

Steve

Nice!

That one goes in my "Cool things Excel can do" workbook.

Thanks Bob

Regards

Steve

On Fri, 7 Apr 2006 01:41:01 -0700, ThomasVike
wrote:

In a cell there is text and numbers, example: ABC123. In an other cell I want
to show the numbers only (123).

I can't find a useful function, any suggestions?

If there is only a single string of digits, as in your example, then Bob's
formula will work fine.

If you have multiple digit strings, as in ab123cd456, then one solution would
be to download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

and use the regular expression formula:

=REGEX.SUBSTITUTE(A1,"\D")

This returns the digits as a text string, so if you need it as a numeric value,
precede the above with a double unary to convert it.

=--REGEX.SUBSTITUTE(A1,"\D")


--ron

In summary, it looks for all the numeric values (and adds 0123456789 to the
end to ensure all are found), and then builds an array of all numeric
strings within the overall string (the MIN is used to find the first one,
and thus where to start). LOOKUP with a lookup value of
9.99999999999999E+307 (which is the largest number that can be stored in a
cell) if it can't find a value (which it shouldn't using
9.99999999999999E+307 <vbg) will return the largest value in the array,
which will be the final string in this case.

Using an example of ABC123 in A1.

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

MIN will the return 4 - self explanatory

ROW(INDIRECT("1:"&LEN(A1))) returns an array of {1;2;3;4;5;6}which is passed
to the MID function to extract sub-strings

MID(A1, SEARCH(...),ROW(...)) then builds an array of numeric substrings
{"1";"12";"123";"123";"123";"123"} - note that we use the length of A1 in
case it is all numeric, but it does mean that the final numeric substring,
123, gets repeated 3 more times because of the 3 letters at the start

The -- is just used to transform the array to numeric values

LOOKUP then lookups 9.99999999999999E+307 in that array, doesn't find it, so
it returns the largest value found, 123

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Gizmo63" wrote in message
...
Hi Bob,

I take it back about needing a macro if variable :-o

Can you explain the elements in this please? With the 9.9... and {} etc
I'm
lost.
Cheers

Giz

"Bob Phillips" wrote:

Regardless of position


=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Scoops" wrote in message
oups.com...
Hi Thomas

If your data is fixed at aaannn then you can use:

=RIGHT(A1,3)

Regards

Steve

As an alternative, you could have a short User Defined function which
examines each character of the string and removes any which are not in
the range 0 to 9.

Pete

On 7 Apr 2006 05:32:51 -0700, "Pete_UK" wrote:

As an alternative, you could have a short User Defined function which
examines each character of the string and removes any which are not in
the range 0 to 9.

Pete

Sure. Anything that can be done with an add-in can be duplicated in VBA.
However, the .xll add-in will run faster, and is quite a bit more flexible than
other solutions.

For example, if there are multiple strings of numbers, you could easily rewrite
the regex to only return the second string; or only return strings that look
like valid dates; or ... The possibilities are endless.


--ron

You guys are awesome. This works great for extracting part numbers off a
database export list that has the part number with the description all
together cells. You just cut my time for this task by 95%!!

I wish there was a preset function on excel, though.

Thanks!

"Bob Phillips" wrote:

Regardless of position

=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"
0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Scoops" wrote in message
oups.com...
Hi Thomas

If your data is fixed at aaannn then you can use:

=RIGHT(A1,3)

Regards

Steve