software

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

SteveHi, 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

quot;ThomasVikequot; wrote:

gt; In a cell there is text and numbers, example: ABC123. In an other cell I want
gt; to show the numbers only (123).
gt;
gt; 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},A1amp;quot;
0123456789quot;)),ROW(INDIRECT(quot;1:quot;amp;LEN(A1)))))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Scoopsquot; gt; wrote in message oups.com...
gt; Hi Thomas
gt;
gt; If your data is fixed at aaannn then you can use:
gt;
gt; =RIGHT(A1,3)
gt;
gt; Regards
gt;
gt; Steve
gt;
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

quot;Bob Phillipsquot; wrote:

gt; Regardless of position
gt;
gt; =LOOKUP(9.99999999999999E 307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1amp;quot;
gt; 0123456789quot;)),ROW(INDIRECT(quot;1:quot;amp;LEN(A1)))))
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Scoopsquot; gt; wrote in message
gt; oups.com...
gt; gt; Hi Thomas
gt; gt;
gt; gt; If your data is fixed at aaannn then you can use:
gt; gt;
gt; gt; =RIGHT(A1,3)
gt; gt;
gt; gt; Regards
gt; gt;
gt; gt; Steve
gt; gt;
gt;
gt;
gt;

Nice!

That one goes in my quot;Cool things Excel can doquot; workbook.

Thanks Bob

Regards

SteveOn Fri, 7 Apr 2006 01:41:01 -0700, ThomasVike
gt; wrote:

gt;In a cell there is text and numbers, example: ABC123. In an other cell I want
gt;to show the numbers only (123).
gt;
gt;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
xcell05.free.fr

and use the regular expression formula:

=REGEX.SUBSTITUTE(A1,quot;\Dquot;)

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,quot;\Dquot;)--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 lt;vbggt;) 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},A1amp;quot;0123456789quot;)) 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(quot;1:quot;amp;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
{quot;1quot;;quot;12quot;;quot;123quot;;quot;123quot;;quot;123quot;;quot;123quot;} - 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)

quot;Gizmo63quot; gt; wrote in message
news
gt; Hi Bob,
gt;
gt; I take it back about needing a macro if variable :-o
gt;
gt; Can you explain the elements in this please? With the 9.9... and {} etc
I'm
gt; lost.
gt; Cheers
gt;
gt; Giz
gt;
gt; quot;Bob Phillipsquot; wrote:
gt;
gt; gt; Regardless of position
gt; gt;
gt; gt;
=LOOKUP(9.99999999999999E 307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1amp;quot;
gt; gt; 0123456789quot;)),ROW(INDIRECT(quot;1:quot;amp;LEN(A1)))))
gt; gt;
gt; gt; --
gt; gt; HTH
gt; gt;
gt; gt; Bob Phillips
gt; gt;
gt; gt; (remove nothere from email address if mailing direct)
gt; gt;
gt; gt; quot;Scoopsquot; gt; wrote in message
gt; gt; oups.com...
gt; gt; gt; Hi Thomas
gt; gt; gt;
gt; gt; gt; If your data is fixed at aaannn then you can use:
gt; gt; gt;
gt; gt; gt; =RIGHT(A1,3)
gt; gt; gt;
gt; gt; gt; Regards
gt; gt; gt;
gt; gt; gt; Steve
gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
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.

PeteOn 7 Apr 2006 05:32:51 -0700, quot;Pete_UKquot; gt; wrote:

gt;As an alternative, you could have a short User Defined function which
gt;examines each character of the string and removes any which are not in
gt;the range 0 to 9.
gt;
gt;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