Extract Numerics only

G

Guest

A little more info on my problem:

This formula works:

=IF(VALUE(RIGHT(C3381,3))=107,7,IF(LEFT(N3381,8)="OFF ROAD","MANUAL
INPUT",VALUE(LEFT(LOOKUP(REPT("z",255),MID(N3381,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},N3381&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})),2))))

By adding to more conditions to the front of it, I get an error:

=IF(N3381="M8",35,IF(VALUE(RIGHT(C3881,3))=814,33,IF(VALUE(RIGHT(C3381,3))=107,7,IF(LEFT(N3381,8)="OFF
ROAD","MANUAL
INPUT",VALUE(LEFT(LOOKUP(REPT("z",255),MID(N3381,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},N3381&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})),2))))))
 
B

Bob Phillips

No Peo, it still gives 114005, it just makes the result a number as against
the string. The reason I even mentioned it was because in Domenic's
original; formula, the -- was by the MID, and as you showed, that won't
work, by preceding it does.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Domenic

Maybe...

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUMPRODUCT(LEN
(A1)-LEN(SUBSTITUTE(A1,{"-",0,1,2,3,4,5,6,7,8,9},""))))

....which will return...

11-4005
040652
3127
2679
20
130

....when you have the following...

OFF 11-4005
ON 040652
ON 3127
ON2679
O/F 20R
OFF///130H

Hope this helps!
 
B

Bob Phillips

Corey,

My formula actually returns 11 from the string OFF 11-4005 as it strips off
anything before the first numeric, and anything after the first non-numeric
after that.

Did you insert the name formula, and array enter everything?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
G

Guest

I know this is a bit late, but I just came up with this, which seems to work
well:

=MID(A1,MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),COUNT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

If finds the first digit location and the count of digits in the string and
uses those values in the MID function.

***********
Regards,
Ron
 
B

Bob Phillips

That sounds like too many nested functions. Put some of the tests in a
separate cell, and then include a test on that cell in the main formula.
That is exactly the problem I got with my formula, which is what I use the
name formula for.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
G

Guest

Nope....it misses punctuation within the numbers, as in:
OFF 11-4005

***********
Regards,
Ron
 
P

Peo Sjoblom

When I use your formula I get 769142 not 114005 also if you use number you
won't get a leading zero as in the OP's second example
--
Regards,

Peo Sjoblom

(No private emails please)
 
G

Guest

I've been playing with this formula, which pulls from the first digit found
thru the last digit found:

=MID(A1,MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1+MAX(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))))-MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0))

Note: Commit that array formula by holding down the [Ctrl] and [Shift] keys
when you press [Enter].

So...
"OFF 11-4005ABC" would result in 11-4005
"OFF///130H" would result in 130
etc

I think that handles all of the pertinent scenarios...let me know.

***********
Regards,
Ron
 
G

Guest

Bob Phillips said:
You might want to convert that to a number

=--LOOKUP(REPT("z",255),MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234567
89
")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}))


--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
G

Guest

Domenic said:
Maybe...

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUMPRODUCT(LEN
(A1)-LEN(SUBSTITUTE(A1,{"-",0,1,2,3,4,5,6,7,8,9},""))))

....which will return...

11-4005
040652
3127
2679
20
130

....when you have the following...

OFF 11-4005
ON 040652
ON 3127
ON2679
O/F 20R
OFF///130H

Hope this helps!
Click to expand...
 
G

Guest

WHO-EVER YOU ARE: a million thanks for your varied posts and replies. I am a
research chemist and needed to find a formula to extract numbers from text
(complex elements from the periodic table) and manipulate them. By sheer luck
and a half hour of trying I lucked out in finding a reply which answered my
search. I have no idea of what I copied but it worked! My name is Eef (Dutch)
and work at the "Chemisse Fabrieken, Simpelveld, Nederland. I say KUDOS to
all of you..
 
G

Guest

Ron - Don't know if you are still monitoring this or will receive an email
notification of this post but I wanted to thank you for this excellent tool.
It's saved me a lot of time and trouble and its much appreciated. That's
besides the fact that its going to make me look good too!

Thanks,
Bill

Ron Coderre said:
I've been playing with this formula, which pulls from the first digit found
thru the last digit found:

=MID(A1,MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1+MAX(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))))-MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0))

Note: Commit that array formula by holding down the [Ctrl] and [Shift] keys
when you press [Enter].

So...
"OFF 11-4005ABC" would result in 11-4005
"OFF///130H" would result in 130
etc

I think that handles all of the pertinent scenarios...let me know.

***********
Regards,
Ron


Ron Coderre said:
Nope....it misses punctuation within the numbers, as in:
OFF 11-4005

***********
Regards,
Ron
Click to expand...
Click to expand...
 
G

Guest

That post is a year and a half old, but feedback is always welcome. Thanks
for taking the time to let me know that formula helped you out., Bill. Much
appreciated.


***********
Best Regards,
Ron

XL2003, WinXP


BurghRocks said:
Ron - Don't know if you are still monitoring this or will receive an email
notification of this post but I wanted to thank you for this excellent tool.
It's saved me a lot of time and trouble and its much appreciated. That's
besides the fact that its going to make me look good too!

Thanks,
Bill

Ron Coderre said:
I've been playing with this formula, which pulls from the first digit found
thru the last digit found:

=MID(A1,MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),1+MAX(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))))-MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0))

Note: Commit that array formula by holding down the [Ctrl] and [Shift] keys
when you press [Enter].

So...
"OFF 11-4005ABC" would result in 11-4005
"OFF///130H" would result in 130
etc

I think that handles all of the pertinent scenarios...let me know.

***********
Regards,
Ron


Ron Coderre said:
Nope....it misses punctuation within the numbers, as in:
OFF 11-4005

***********
Regards,
Ron


:

I know this is a bit late, but I just came up with this, which seems to work
well:

=MID(A1,MATCH(1,--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),COUNT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

If finds the first digit location and the count of digits in the string and
uses those values in the MID function.

***********
Regards,
Ron


:

Hello. I have a column with data that contain numerics and text:

ON 3127
ON2679
O/F 20R
OFF///130H

Is there a formula I can input in an adjacent cell to only pull out the
numbers to give me the following:

3127
2679
20
130

Any help is appreciated. Thanks!
Click to expand...
Click to expand...
Click to expand...
 
K

katdot

This is the only one that works for me, but I lose the last number. My values
look more like this:

1+0756
1+0789AH
1+0478BK

I know this is an old topic, but hopefully someone can help.
 
K

katdot

I need to pull out just the numbers. So ...

1+0756 10756
1+0789AH 10789.

The formula from Domenic gives me results, but chops off the last number. So
....

1+0478BK 1+047
1+0756 1+075
1+0789AH 1+078

I don't have enough knowledge to figure out what's going wrong.
 
R

Ron Rosenfeld

This is the only one that works for me, but I lose the last number. My values
look more like this:

1+0756
1+0789AH
1+0478BK

I know this is an old topic, but hopefully someone can help.
Click to expand...

This UDF will return all the numbers in the string. If there are NO numbers,
it returns #VALUE! That can be modified, if you wish.

================================
Option Explicit
Function Nums(str As String) As Double
Dim re As Object, mc As Object
Const sPat As String = "\D"
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = sPat
Nums = re.Replace(str, "")
End Function
===================================

To enter this <alt-F11> opens the VBEditor. Ensure your project is highlighted
in the project explorer window, then Insert/Module and paste the code above
into the window that opens.

Then use the formula =Nums(cell_ref) on your worksheet.
--ron
 
R

Ron Coderre

Solution from Harlan Grove
A1: abc123def456ghi789

First, create a Named Formula
Names in Workbook: Seq
Refers to: =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))

This ARRAY FORMULA
(committed with CTRL+SHIFT+ENTER, instead of just ENTER)
removes ALL non-numerics from a string.

In sections, for readability:
B1: =SUM(IF(ISNUMBER(1/(MID(A1,seq,1)+1)),MID(A1,seq,1)*
10^MMULT(-(seq<TRANSPOSE(seq)),-ISNUMBER(1/(MID(A1,seq,1)+1)))))

In the example, the formula returns: 123456789

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
K

katdot

That worked beautifully. It even copied into the attachment to send the boss.

Thank you so much! I just hope my co-workers don't think this means I have
all the answers. :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formula to extract number values only 8
Making certain a cell contains numerics 2
Extracting number from Text and () 15
Sumif Question? 8
Extract number from two delimiter 3
Excel 2002: How to extract characters ? 5
query for specific data configuration 4
Extracting 7 digit number from alphanumeric string 10

Top