Searching String

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Greg - 10 Oct 2006 01:37 GMT

I would like to extract numeric values from a alphanumeric field. I can't
find an example of what I am trying to do.
Example;

1. Cell contains "bpnth500unwumm". Looking for a result in a different cell
500.

2. Cell contains "Natp350rummunw". Looking for a result in a different cell
of 1350.

3.Cell contains "napt450rummunw56". Looking for a result in a different cell
of 450.

4. Cell contains "a2000ronNa". Looking for a result in a different cell of
2000.

5. Cell contains c40bronmAA16. Looking for a result in a different cell of
40.

I have several hundreds or these entries (all in 1 column) I am trying to
convert.

Any assistance would be appreciated and thanks in advance.

Greg

Reply to this Message

Ron Rosenfeld - 10 Oct 2006 04:17 GMT

>I would like to extract numeric values from a alphanumeric field. I can't
>find an example of what I am trying to do.
[quoted text clipped - 21 lines]
>
>Greg

I have assumed, based on your examples, that you wish to extract the FIRST
series of consecutive numbers in the string, and that these will always be
INTEGERS.

That being the case, one way is to download and install Longre's free
morefunc.xll add-in from: http://xcell05.free.fr/

This add-in can be easily distributed with your workbook if that is an issue.

Then use the Regular Expression formula:

=REGEX.MID(A1,"\d+")

Here is your data and the results:

bpnth500unwumm        500
Natp350rummunw        350
napt450rummunw56    450
a2000ronNa        2000
c40bronmAA16        40

The above formula will return the numeric values as a TEXT string. If you
require that they be evaluated as NUMBERS, then precede the formula with a
double unary to convert:

=--REGEX.MID(A1,"\d+")

--ron

Reply to this Message

RagDyeR - 10 Oct 2006 15:30 GMT

And of course, we also have the XL resident functions:

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

Signature

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

>I would like to extract numeric values from a alphanumeric field. I can't
>find an example of what I am trying to do.
[quoted text clipped - 23 lines]
>
>Greg

I have assumed, based on your examples, that you wish to extract the FIRST
series of consecutive numbers in the string, and that these will always be
INTEGERS.

That being the case, one way is to download and install Longre's free
morefunc.xll add-in from: http://xcell05.free.fr/

This add-in can be easily distributed with your workbook if that is an
issue.

Then use the Regular Expression formula:

=REGEX.MID(A1,"\d+")

Here is your data and the results:

bpnth500unwumm 500
Natp350rummunw 350
napt450rummunw56 450
a2000ronNa 2000
c40bronmAA16 40

The above formula will return the numeric values as a TEXT string. If you
require that they be evaluated as NUMBERS, then precede the formula with a
double unary to convert:

=--REGEX.MID(A1,"\d+")

--ron

Reply to this Message

Ron Rosenfeld - 10 Oct 2006 17:01 GMT

>And of course, we also have the XL resident functions:
>
>=LOOKUP(99^99,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

It's good to have both available. Some users cannot download and install
add-ins due to corporate policies.

But now that I've gotten more used to Regular Expressions, I find them simpler
to use, which means less time spent solving problems.
--ron

Reply to this Message