A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Extract Numerics only



 
 
Thread Tools Display Modes
  #1  
Old December 1st, 2005, 06:26 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old December 1st, 2005, 07:10 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old December 1st, 2005, 07:41 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old December 1st, 2005, 08:51 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old December 1st, 2005, 08:56 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old December 1st, 2005, 09:49 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old December 1st, 2005, 09:54 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old December 1st, 2005, 10:18 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old December 1st, 2005, 10:22 PM posted to microsoft.public.excel.misc
external usenet poster
 
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  
Old December 1st, 2005, 10:52 PM posted to microsoft.public.excel.misc
external usenet poster
 
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!



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
a tip about how to extract .SWFfile from PowerPoint file SusanZheng Powerpoint 1 November 3rd, 2005 04:58 AM
Extract records with a specific field appearing more than once in the DB markx Running & Setting Up Queries 7 September 28th, 2005 06:44 PM
Trendline Extract Phil Hageman Charts and Charting 5 July 6th, 2005 02:27 AM
Extract Unique Values, Then Extract Again to Remove Suffixes Karl Burrows General Discussion 23 June 25th, 2005 10:37 PM
Extract specific data into its own workbook via macro? Adrian B General Discussion 2 February 24th, 2005 07:09 AM


All times are GMT +1. The time now is 03:42 PM.


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