A user defined function (UDF) like the one below might work for you - it's
not complete or foolproof, but is a good basis for one.
To use it, first put it into your workbook:
Open the workbook. Press [Alt]+[F11] to enter the VB Editor and in the VBE,
choose Insert | Module and copy and paste the code below into the empty code
module presented to you. Close the VB Editor.
To use it in a worksheet, enter a formula such as
=GetNumGroup(A5)
where A5 is the cell containing the text you want to find the number group
in. So, if
A5 has "hello 1234-9876" in it, whatever cell you enter the =GetNumGroup(A5)
should display "1234-9876".
If no 4-4 group is found, the cell with the formula will remain blank.
Hope this helps a bit.
The code:
Function GetNumGroup(whatCell) As String
'only works properly if there's just one
'group of possible digits.
'Examples:
' hello 1234-5678 goodbye
'would be ok and found, but
' hello 1234-5678 goodbye 4ever
'would fail because the result would be
'1234-56784
'
Const charList = "-0123456789"
Dim workingString As String
Dim resultString As String
Dim LC As Integer
workingString = whatCell
If Len(workingString) > 8 Then
'has to be at least 9 long to contain a 1234-4321 entry!
For LC = 1 To Len(workingString)
If InStr(charList, Mid(workingString, LC, 1)) Then
resultString = resultString & Mid(workingString, LC, 1)
End If
Next
End If
If Len(resultString) = 9 And Mid(resultString, 5, 1) = "-" Then
GetNumGroup = resultString
Else
GetNumGroup = ""
End If
End Function
MeatLightning said:
Hey all -
I'm trying to extract a number from a text string. The text string
varies in length and contents. The only thing that uniquely identifies the
data I need to extract is its format - specifically: The number is always 4
digits separated by a dash followed by 4 more digits. For example: 1234-1234.
The trick is that there are other numbers in there that come close to the
same format (ex: 12-1234).
Any suggestions?
Thanks in advance!
-meat