Extract Numerics only

On Thu, 1 Dec 2005 09:26:02 -0800, "Corey"
wrote:

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!

If you just have single numbers, as in the above, then

1. Download and install Laurent Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

2. Then use this formula:

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


--ron

On Thu, 01 Dec 2005 17:58:01 -0500, Ron Rosenfeld
wrote:

On Thu, 1 Dec 2005 09:26:02 -0800, "Corey"
wrote:

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!

If you just have single numbers, as in the above, then

1. Download and install Laurent Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

2. Then use this formula:

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


--ron

The following formula will also work, and have the advantage of being able to
pull out ALL numbers -- even if they are not contiguous:

=REGEX.SUBSTITUTE(A1,"[^0-9]","")



--ron

What a good idea. If you don't want to install that DLL, you could use
Harlan Grove's SubStr regular expression UDF, and then use this formula

=--SubStr(SubStr(A1,"[^0-9 \t]{2,}"," "),"[^0-9]","")

'-----------------------------------------
Function Substr(orig_text As String, _
match_pat As String, _
replace_pat As String, _
Optional instance As Variant) As Variant
'-----------------------------------------
'Similar to Excel's SUBSTITUTE but using VBScript's perl-like regexps
'Required:
' orig_text - string to search through,
' match_pat - regexp to find,
' replace_pat - replacement pattern
'Optional:
' instance - which matched substring to replace or 0 for all (default)
'-----------------------------------------
Dim regex As Object, matches As Object, m As Object

If IsMissing(instance) Then
instance = 0
ElseIf TypeName(instance) "Double" Then
Substr = CVErr(xlErrValue) 'invalid instance type
instance = -1
ElseIf CDbl(instance) = 0.5 Then
Substr = CVErr(xlErrNum) 'invalid instance value
instance = -1
Else
instance = Int(instance + 0.5)
End If

If instance = -1 Then Exit Function 'do nothing quickly

Set regex = CreateObject("vbscript.regexp")
regex.Pattern = match_pat
regex.Global = True

If instance = 0 Then
Substr = regex.Replace(orig_text, replace_pat)
Else
Set matches = regex.Execute(orig_text)
If instance matches.Count Then
Substr = orig_text 'matchnum out of bounds - do nothing
Else
Set m = matches.Item(instance - 1)
Substr = Left(orig_text, m.FirstIndex) & _
regex.Replace(m.Value, replace_pat) & _
Right(orig_text, Len(orig_text) - m.FirstIndex - m.Length)
End If
End If

End Function

--

HTH

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


"Ron Rosenfeld" wrote in message
...
On Thu, 1 Dec 2005 09:26:02 -0800, "Corey"

wrote:

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!

If you just have single numbers, as in the above, then

1. Download and install Laurent Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

2. Then use this formula:

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


--ron

Taking Ron's lead, just use

=---SubStr(A1,"[^0-9]","")

--

HTH

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


"Bob Phillips" wrote in message
...
What a good idea. If you don't want to install that DLL, you could use
Harlan Grove's SubStr regular expression UDF, and then use this formula

=--SubStr(SubStr(A1,"[^0-9 \t]{2,}"," "),"[^0-9]","")

'-----------------------------------------
Function Substr(orig_text As String, _
match_pat As String, _
replace_pat As String, _
Optional instance As Variant) As Variant
'-----------------------------------------
'Similar to Excel's SUBSTITUTE but using VBScript's perl-like regexps
'Required:
' orig_text - string to search through,
' match_pat - regexp to find,
' replace_pat - replacement pattern
'Optional:
' instance - which matched substring to replace or 0 for all (default)
'-----------------------------------------
Dim regex As Object, matches As Object, m As Object

If IsMissing(instance) Then
instance = 0
ElseIf TypeName(instance) "Double" Then
Substr = CVErr(xlErrValue) 'invalid instance type
instance = -1
ElseIf CDbl(instance) = 0.5 Then
Substr = CVErr(xlErrNum) 'invalid instance value
instance = -1
Else
instance = Int(instance + 0.5)
End If

If instance = -1 Then Exit Function 'do nothing quickly

Set regex = CreateObject("vbscript.regexp")
regex.Pattern = match_pat
regex.Global = True

If instance = 0 Then
Substr = regex.Replace(orig_text, replace_pat)
Else
Set matches = regex.Execute(orig_text)
If instance matches.Count Then
Substr = orig_text 'matchnum out of bounds - do nothing
Else
Set m = matches.Item(instance - 1)
Substr = Left(orig_text, m.FirstIndex) & _
regex.Replace(m.Value, replace_pat) & _
Right(orig_text, Len(orig_text) - m.FirstIndex - m.Length)
End If
End If

End Function

--

HTH

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


"Ron Rosenfeld" wrote in message
...
On Thu, 1 Dec 2005 09:26:02 -0800, "Corey"

wrote:

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!

If you just have single numbers, as in the above, then

1. Download and install Laurent Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

2. Then use this formula:

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


--ron

Thanks for all the suggestions people. I'm not to familiar with VBA nor UDF.
Also, I'm trying to put together a tool for a below novice user of Excel as
it takes her hours to decipher a statement. I was hoping to use just a simple
formula and have her cut and paste the data to have it automatically populate
with this formula. I've tried yours Domenic, and it seems to work for the
most part, except for some:

OFF 11-4005 returns 769142
ON 040652 returns 40652 (losing the first 0)




"Domenic" wrote:

If your numbers are not more than 15 digits in length, try...

=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15}))

Otherwise, try...

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

Hope this helps!

In article ,
"Corey" wrote:

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!

Thanks for the suggestion Dave. I tried your VBA code and it gets hung up on
the "Next K" portion. ???



"Dave O" wrote:

The only way I can think to do this is with VBA code. This code works
on a column of numbers, and will place the numeric-only string in the
column immediately to the right of the original column. Type the word
"stop" (no quotes) in the cell under the last alpha-numeric cell you
want to evaluate.

Note- this code formats the cells that will hold the numeric-only
values as text to preserve any leading zeroes. See the comments within
the code.

Sub RemoveText()
Dim Werd As String, NewWerd As String
Dim K As Byte

Do Until ActiveCell.Value = "stop" 'start main loop
Werd = ActiveCell.Value 'store part number to memory
For K = 1 To Len(Werd) 'strip out non-numeric characters
If Asc(Mid(Werd, K, 1)) = 48 And Asc(Mid(Werd, K, 1)) = 57 Then
NewWerd = NewWerd & Mid(Werd, K, 1)
Next K

ActiveCell.Offset(0, 1).Select 'move to adjacent column
Selection.NumberFormat = "@" 'format cell as text to preserve
leading zero, if any
ActiveCell.Value = NewWerd 'apply numeric value
ActiveCell.Offset(0, -1).Select 'move back
NewWerd = "" 'set value to nothing
ActiveCell.Offset(1, 0).Select 'move down one cell
Loop

End Sub

The reason the first returns 769142 is that the extracted value 11-4005
is seen as a date so Excel translates the date as 11/01/05 which is 769142
days after Jan 0 1900 Excel's first date, given that the
numbers can be so different it is hard to guard against, try this adaptation
of Domenic's excellent formula

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


--

Regards,

Peo Sjoblom

"Corey" wrote in message
...
Thanks for all the suggestions people. I'm not to familiar with VBA nor
UDF.
Also, I'm trying to put together a tool for a below novice user of Excel
as
it takes her hours to decipher a statement. I was hoping to use just a
simple
formula and have her cut and paste the data to have it automatically
populate
with this formula. I've tried yours Domenic, and it seems to work for the
most part, except for some:

OFF 11-4005 returns 769142
ON 040652 returns 40652 (losing the first 0)




"Domenic" wrote:

If your numbers are not more than 15 digits in length, try...


=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15}))

Otherwise, try...


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

Hope this helps!

In article ,
"Corey" wrote:

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!

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)


"Peo Sjoblom" wrote in message
...
The reason the first returns 769142 is that the extracted value 11-4005
is seen as a date so Excel translates the date as 11/01/05 which is 769142
days after Jan 0 1900 Excel's first date, given that the
numbers can be so different it is hard to guard against, try this
adaptation
of Domenic's excellent formula


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


--

Regards,

Peo Sjoblom

"Corey" wrote in message
...
Thanks for all the suggestions people. I'm not to familiar with VBA nor
UDF.
Also, I'm trying to put together a tool for a below novice user of Excel
as
it takes her hours to decipher a statement. I was hoping to use just a
simple
formula and have her cut and paste the data to have it automatically
populate
with this formula. I've tried yours Domenic, and it seems to work for
the
most part, except for some:

OFF 11-4005 returns 769142
ON 040652 returns 40652 (losing the first 0)




"Domenic" wrote:

If your numbers are not more than 15 digits in length, try...


=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15}))

Otherwise, try...


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

Hope this helps!

In article ,
"Corey" wrote:

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!

I believe the OP complained that he got 769142 instead of 11-4005 thus the
change, otherwise one might as well keep Domenic's original formula


--

Regards,

Peo Sjoblom

"Bob Phillips" wrote in message
...
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)


"Peo Sjoblom" wrote in message
...
The reason the first returns 769142 is that the extracted value 11-4005
is seen as a date so Excel translates the date as 11/01/05 which is
769142
days after Jan 0 1900 Excel's first date, given that the
numbers can be so different it is hard to guard against, try this
adaptation
of Domenic's excellent formula



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


--

Regards,

Peo Sjoblom

"Corey" wrote in message
...
Thanks for all the suggestions people. I'm not to familiar with VBA
nor
UDF.
Also, I'm trying to put together a tool for a below novice user of
Excel
as
it takes her hours to decipher a statement. I was hoping to use just a
simple
formula and have her cut and paste the data to have it automatically
populate
with this formula. I've tried yours Domenic, and it seems to work for
the
most part, except for some:

OFF 11-4005 returns 769142
ON 040652 returns 40652 (losing the first 0)




"Domenic" wrote:

If your numbers are not more than 15 digits in length, try...



=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15}))

Otherwise, try...



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

Hope this helps!

In article ,
"Corey" wrote:

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!

This works perfect Peo! Thanks a bunch! Also to you Domenic for coming up
with that original formula. I tried Bob's suggestion to this as well, but got
#VALUE!. Since your formula returns the everything to the right, starting
with the first numeric, I just added VALUE(LEFT(A1,2)) to pull out the first
two numbers. This is the division number I've been trying to get at. Perhaps
if I said that earlier, there's another way to get it done. ?? Anyways, I've
also added two if statements to the beginning of that and it works great.
When I try to add a third I get an error that leads me to the "SEARCH"
portion of the formula. Is there a limit to how many conditionals I can put
in this thing?

Thanks again!

"Peo Sjoblom" wrote:

The reason the first returns 769142 is that the extracted value 11-4005
is seen as a date so Excel translates the date as 11/01/05 which is 769142
days after Jan 0 1900 Excel's first date, given that the
numbers can be so different it is hard to guard against, try this adaptation
of Domenic's excellent formula

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


--

Regards,

Peo Sjoblom

"Corey" wrote in message
...
Thanks for all the suggestions people. I'm not to familiar with VBA nor
UDF.
Also, I'm trying to put together a tool for a below novice user of Excel
as
it takes her hours to decipher a statement. I was hoping to use just a
simple
formula and have her cut and paste the data to have it automatically
populate
with this formula. I've tried yours Domenic, and it seems to work for the
most part, except for some:

OFF 11-4005 returns 769142
ON 040652 returns 40652 (losing the first 0)




"Domenic" wrote:

If your numbers are not more than 15 digits in length, try...


=LOOKUP(9.99999999999999E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A
1&"0123456789")),{1,2,3,4,5,6,7,8,9,10,11,12,13,14 ,15}))

Otherwise, try...


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

Hope this helps!

In article ,
"Corey" wrote:

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!