Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Corey
 
Posts: n/a
Default Extract Numerics only

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!
  #2   Report Post  
Posted to microsoft.public.excel.misc
Dave O
 
Posts: n/a
Default Extract Numerics only

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

  #3   Report Post  
Posted to microsoft.public.excel.misc
Gord Dibben
 
Posts: n/a
Default Extract Numerics only

Corey

Suggest a UDF if you want a formula to extract numbers to another cell and
leave original data in place.

Function DeleteNonNumerics(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

If you want to strip in place use a macro after selecting the column.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub


Gord Dibben Excel MVP


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!


  #4   Report Post  
Posted to microsoft.public.excel.misc
Dennis
 
Posts: n/a
Default Extract Numerics only

Gord,

Best way to avoid an error in the function if the cell does not have any
numerics?

Thanks for your knowledge and time!

Dennis




"Gord Dibben" wrote:

Corey

Suggest a UDF if you want a formula to extract numbers to another cell and
leave original data in place.

Function DeleteNonNumerics(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

If you want to strip in place use a macro after selecting the column.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub


Gord Dibben Excel MVP


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!



  #5   Report Post  
Posted to microsoft.public.excel.misc
Dennis
 
Posts: n/a
Default Extract Numerics only

NOTE: If you use any UDF's, one must place the UDF in "ThisWorkbook" as it
will not work from i.e. Personal.xls




"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!



  #6   Report Post  
Posted to microsoft.public.excel.misc
Gord Dibben
 
Posts: n/a
Default Extract Numerics only

Until someone comes up with a better answer.....

=IF(ISERROR(deletenonnumerics(J6)),"",deletenonnum erics(J6))


Gord

On Thu, 1 Dec 2005 11:51:02 -0800, Dennis
wrote:

Gord,

Best way to avoid an error in the function if the cell does not have any
numerics?

Thanks for your knowledge and time!

Dennis




"Gord Dibben" wrote:

Corey

Suggest a UDF if you want a formula to extract numbers to another cell and
leave original data in place.

Function DeleteNonNumerics(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

If you want to strip in place use a macro after selecting the column.

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub


Gord Dibben Excel MVP


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!




  #7   Report Post  
Posted to microsoft.public.excel.misc
Gord Dibben
 
Posts: n/a
Default Extract Numerics only

Dennis

You can a UDF from Personal.xls if you qualify it in the formula.

=Personal.xls!udfname(arguments)


Gord Dibben Excel MVP

On Thu, 1 Dec 2005 11:56:02 -0800, Dennis
wrote:

NOTE: If you use any UDF's, one must place the UDF in "ThisWorkbook" as it
will not work from i.e. Personal.xls




"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!


  #8   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Extract Numerics only

Corey,

A formula approach.

First, this is based upon the data being in A1:An, so adjust all formulae to
suit.

Insert an Excel name (InsertNameDefine...), with a name of pos_array, and
a RefersTo value of
=ROW(INDIRECT("A1:A"&LEN('3'!A1)))

Then in B1, add this formula

=IF(ISNUMBER(--LEFT(A1,1)),LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,pos_a rray,1))),p
os_array,255))-1),MID(A1,MIN(IF(ISERROR(1*(MID(A1,pos_array,1))), 255,pos_arr
ay)),99))

as an array formula, which means that it is committed with Ctrl-Shift-Enter,
and copy down.

You will see that for ON 3127, you already get the end-result, but not for
O/F 20R. So we need to do one more iteration of this. So, copy B1 over to
C1, and then down, the second iteration should get you your end-results.

If you want the end-result as a number, rather than the text that these
formulae produce, use a slight variation in C1

=--(IF(ISNUMBER(--LEFT(A1,1)),LEFT(A1,MIN(IF(ISERROR(1*(MID(A1,pos_a rray,1))
),pos_array,255))-1),MID(A1,MIN(IF(ISERROR(1*(MID(A1,pos_array,1))), 255,pos_
array)),99)))

again as an array formula, and again copy down.

--

HTH



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


"Corey" wrote in message
...
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!



  #9   Report Post  
Posted to microsoft.public.excel.misc
Domenic
 
Posts: n/a
Default Extract Numerics only

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!

  #10   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Extract Numerics only

Very good. A bit better than mine :-))

--

HTH

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


"Domenic" wrote in message
...
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!





  #11   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Extract Numerics only

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





  #12   Report Post  
Posted to microsoft.public.excel.misc
Corey
 
Posts: n/a
Default Extract Numerics only

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!


  #13   Report Post  
Posted to microsoft.public.excel.misc
Corey
 
Posts: n/a
Default Extract Numerics only

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


  #14   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default Extract Numerics only

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!




  #15   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Extract Numerics only

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!







  #16   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default Extract Numerics only

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!







  #17   Report Post  
Posted to microsoft.public.excel.misc
Corey
 
Posts: n/a
Default Extract Numerics only

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!




  #18   Report Post  
Posted to microsoft.public.excel.misc
Corey
 
Posts: n/a
Default Extract Numerics only

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,M IN(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)="O FF
ROAD","MANUAL
INPUT",VALUE(LEFT(LOOKUP(REPT("z",255),MID(N3381,M IN(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))))))




"Corey" wrote:

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!




  #19   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Extract Numerics only

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)


"Peo Sjoblom" wrote in message
...
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!









  #20   Report Post  
Posted to microsoft.public.excel.misc
Domenic
 
Posts: n/a
Default Extract Numerics only

Maybe...

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),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!

In article ,
"Corey" wrote:

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!




  #21   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Extract Numerics only

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)


"Corey" wrote in message
...
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!






  #22   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default Extract Numerics only

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


"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!

  #23   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Extract Numerics only

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)


"Corey" wrote in message
...
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,M IN(SEARCH({0,1,2,3,4,5,6,7
,8,9},N3381&"0123456789")),{1,2,3,4,5,6,7,8,9,10,1 1,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))=1
07,7,IF(LEFT(N3381,8)="OFF
ROAD","MANUAL

INPUT",VALUE(LEFT(LOOKUP(REPT("z",255),MID(N3381,M IN(SEARCH({0,1,2,3,4,5,6,7
,8,9},N3381&"0123456789")),{1,2,3,4,5,6,7,8,9,10,1 1,12,13,14,15})),2))))))




"Corey" wrote:

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!






  #24   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default Extract Numerics only

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

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


"Ron Coderre" wrote:

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


"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!

  #25   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default Extract Numerics only

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)


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


"Peo Sjoblom" wrote in message
...
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!












  #26   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default A Different approach that may work

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(INDIREC T("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" wrote:

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

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


"Ron Coderre" wrote:

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


"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!

  #27   Report Post  
Posted to microsoft.public.excel.misc
wjohnson
 
Posts: n/a
Default Extract Numerics only


this may not be what you want - but I would take both or 1 column at a
time and past it into a blank "WORD" document - then go to find and
replace and under the "MORE" tab - Select "SPECIAL" and then delete
Charaters or Digets as required.

Not as elegant as functions and formulas - but alot easier.


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.hightechtalks.com/m390
View this thread: http://www.hightechtalks.com/t2299087

  #28   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Extract Numerics only



"Bob Phillips" wrote:

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!






  #29   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Extract Numerics only



"Domenic" wrote:

Maybe...

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),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!

In article ,
"Corey" wrote:

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!


  #30   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Extract Numerics only

WHO-EVER YOU A 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..

"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!



  #31   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default A Different approach that may work

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" wrote:

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(INDIREC T("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" wrote:

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

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


"Ron Coderre" wrote:

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


"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!

  #32   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 698
Default A Different approach that may work

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" wrote:

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" wrote:

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(INDIREC T("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" wrote:

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

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


"Ron Coderre" wrote:

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


"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!

  #33   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Extract Numerics only

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.


"Domenic" wrote:

Maybe...

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),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!

In article ,
"Corey" wrote:

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!


  #34   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Extract Numerics only

1+0756
1+0789AH
1+0478BK

What results do you expect/want?


--
Biff
Microsoft Excel MVP


"katdot" wrote in message
...
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.


"Domenic" wrote:

Maybe...

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),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!

In article ,
"Corey" wrote:

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!




  #35   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Extract Numerics only

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.

"T. Valko" wrote:

1+0756
1+0789AH
1+0478BK

What results do you expect/want?


--
Biff
Microsoft Excel MVP


"katdot" wrote in message
...
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.


"Domenic" wrote:

Maybe...

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),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!

In article ,
"Corey" wrote:

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!







  #36   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default Extract Numerics only

On Tue, 8 Jan 2008 11:20:02 -0800, katdot
wrote:

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.


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
  #37   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,118
Default Extract Numerics only


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)


"katdot" wrote in message
...
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.

"T. Valko" wrote:

1+0756
1+0789AH
1+0478BK

What results do you expect/want?


--
Biff
Microsoft Excel MVP


"katdot" wrote in message
...
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.


"Domenic" wrote:

Maybe...

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),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!

In article ,
"Corey" wrote:

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!








  #38   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Extract Numerics only

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

  #39   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Extract Numerics only

The Ron's are on a roll. This worked too. I might go with this, since I've
very little knowledge of macros in excel. I'm more comfortable with formulas
and functions, but it's nice to know I have more than one option.

You guys are great! Thanks!

"Ron Coderre" wrote:


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)


"katdot" wrote in message
...
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.

"T. Valko" wrote:

1+0756
1+0789AH
1+0478BK

What results do you expect/want?


--
Biff
Microsoft Excel MVP


"katdot" wrote in message
...
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.


"Domenic" wrote:

Maybe...

=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),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!

In article ,
"Corey" wrote:

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!









  #40   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default Extract Numerics only

On Wed, 9 Jan 2008 12:54:04 -0800, katdot
wrote:

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


Glad to help. Thanks for the feedback.
--ron
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extract sub string sixbeforedawn Excel Worksheet Functions 2 October 24th 05 09:50 AM
VBA code to extract m-coefficient in linear trendlines from ALL charts willinusf Excel Discussion (Misc queries) 3 July 12th 05 09:54 PM
Trendline Extract Phil Hageman Charts and Charting in Excel 5 July 6th 05 02:27 AM
Extract Unique Values, Then Extract Again to Remove Suffixes Karl Burrows Excel Discussion (Misc queries) 23 June 25th 05 10:37 PM
Extract specific data into its own workbook via macro? Adrian B Excel Discussion (Misc queries) 2 February 24th 05 07:09 AM


All times are GMT +1. The time now is 04:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"