extracting numbers from alphanumeric strings

[mark kubicki]

does anyone have a formula/function to extract numbers from alphanumeric
strings?
the numeric portion of the string may contain both leading and trailing "0"s
as well as "-"s; (spaces would not be part of the numeric portion, however
they may occur in the string)

ex: "abc05-0730 def" should return: 05-0730

(there's something in Microsoft online, but it doesn't handle trailing 0's
and seems to have other issues with "certain" string combinations... UGH!)

thanks in advance,
mark

 

[Sandy]

Here try this...it isn't fullproof but it will get you close. What this
does is finds the first number coming from the left, then the right and
extracts that part of the string.

****Make sure before running this code that you have an empty column to
the right of the column you'd like to extract the numbers from*******

I'd recommend playing with this on a spare sheet until you see what it
can do for you, especially if you are not familiar with
macros...remember you can not undo a change that a macro has made!

To use this code:

Open VBE (Alt + F11)
Click Insert ==> Module
Paste code
***Insert column to right of number extraction column***
Select the column (or vertical range) you'd like to extract numbers
from
Run macro (Alt + F8) select "GetNumber" and click play

'************************
Sub GetNumber()
Dim mCell As Range
Dim i As Integer, j As Integer
Dim mleft As Integer, mright As Integer
For Each mCell In Selection
For i = 1 To Len(mCell.Value) Step 1
If IsNumeric(Mid(mCell.Value, i, 1)) Then
mleft = i
Exit For
End If
Next i
For j = Len(mCell.Value) To 1 Step -1
If IsNumeric(Mid(mCell.Value, j, 1)) Then
mright = j
Exit For
End If
Next j
mCell.Offset(0, 1).Value = Mid(mCell.Value, i, (j - i) + 1)
Next mCell
End Sub
'********************

hope this works for ya...

Sandy

 

[Walt]

This snippet will build a string with all the numeric and "-" characters in
the string passed to it.

For i = 1 To Len(strFilename)
If IsNumeric(Mid(strFilename, i, 1)) _
or Mid(strFilename, i, 1) = "-" Then
strSN = strSN & Mid(strFilename, i, 1)
End If
Next i

Hope it helps

Walt