Searching String

G

Greg

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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
R

RagDyeR

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)))))

--

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.
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
Click to expand...

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
 
R

Ron Rosenfeld

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)))))
Click to expand...


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
 
Top