Split a text down

[richard]

Hi

S.Cunliffe Completed Job No. 00587.003718.014
I wish to break the above text down using the numbers seperated by a full
stop above.
Each surveyors name will change is size.
I can use Right for the 014 part of the number
The format for the numbers will always be the same length

Can someone please tell me how to break the number down into 3 fields as
follows

1 00587
2 003718
3 014

Thanks

Richard

 

[Brendan Reynolds]

Hi

S.Cunliffe Completed Job No. 00587.003718.014
I wish to break the above text down using the numbers seperated by a full
stop above.
Each surveyors name will change is size.
I can use Right for the 014 part of the number
The format for the numbers will always be the same length

Can someone please tell me how to break the number down into 3 fields as
follows

1 00587
2 003718
3 014

Thanks

Richard

Assuming that the numbers are always directly preceded by the text "job no.
" ...

Public Function GetNumbers(ByVal TextIn) As String()

GetNumbers = Split(Mid$(TextIn, InStr(1, LCase$(TextIn), "job no. ") +
Len("job no. ")), ".")

End Function

Public Sub TestGetNumbers()

Const strcTest As String = "S.Cunliffe Completed Job No.
00587.003718.014"
Dim astrTest As Variant
Dim lngLoop As Long

astrTest = GetNumbers(strcTest)
For lngLoop = LBound(astrTest) To UBound(astrTest)
Debug.Print astrTest(lngLoop)
Next lngLoop

End Sub

Result of calling the test sub in the Immediate window ...

testgetnumbers
00587
003718
014