Here is an updated version with the verification. I also followed Lisa's
example and used more readable parameters.

Note: the code uses >7 rather than =9 because one of your examples used
eight digits.


Function GetNumber(OrigText As Variant, SelType As String) As String



' Place the following in the cell  to call the UDF

'

' =getnumber(A1,"Text")for the text  portion

' =getnumber(A1,"Number")for the number

'



Dim BeforeNumber As Boolean

Dim FullNumStr As String

Dim i As Long

Dim NumStr As String

Dim TxtAfter As String

Dim TxtBefore As String

Dim TmpStr As String

Dim Words As Variant

Dim WorkingText As String



WorkingText = Trim(OrigText)



NumStr = ""

TxtAfter = ""

TxtBefore = ""

BeforeNumber = True



' Split the string into an array of elements

Words = Split(WorkingText, " ")



For i = 0 To UBound(Words)



' Remove the dashes from a string that potentially may be a number

TmpStr = Replace(Words(i), "-", "")



' Is this a string of number and has the version with the dashes at
least eight characters?

If IsNumeric(TmpStr) And Len(Words(i)) > 7 Then

NumStr = TmpStr

FullNumStr = Words(i)

BeforeNumber = False

Else

TmpStr = " " + Words(i)

If BeforeNumber Then TxtBefore = TxtBefore + TmpStr Else TxtAfter =
TxtAfter + TmpStr

End If



Next i



' Verification

TmpStr = Trim(Trim(TxtBefore + " " + FullNumStr) + TxtAfter)



If TmpStr <> OrigText Then

GetNumber = "Problem with routine"

Else

GetNumber = IIf(SelType = "Number", NumStr, Trim(TxtBefore + TxtAfter))

End If



End Function