I am learning some vba and am trying to delete some useless info both before
and after my needed info (job #)

ex. "hello:07-021 goodbye"

I need to have the "hello:" and " goodbye" leaving only the job number.
Note: all the job numbers start with two digits, have a hyphen, and end with
three digits such as "00-000"

Any help would be great . . . thank you

If your text can **never** have a hyphen in **front** of the job number,
then you can do this..

Text = "hello:07-021 goodbye"
JobNum = Mid(Text, InStr(Text, "-") - 2, 6)

--
Rick (MVP - Excel)


"jorlypong" wrote in message
...
I am learning some vba and am trying to delete some useless info both
before
and after my needed info (job #)

ex. "hello:07-021 goodbye"

I need to have the "hello:" and " goodbye" leaving only the job number.
Note: all the job numbers start with two digits, have a hyphen, and end
with
three digits such as "00-000"

Any help would be great . . . thank you

You could set up the following user defined function using VBA and then
within your workbook the function could be used much like a built in
Worksheet function.

'--------------------------------------------

Function GetJobNum(myRange As Range) As String

Dim strResult As String
strResult = ""

Set objRegEx = CreateObject("VBScript.RegExp")

objRegEx.Global = True
objRegEx.Pattern = "\d{2}-\d{3}"

Set colMatches = objRegEx.Execute(myRange.Value)

If colMatches.Count 0 Then
For Each objMatch In colMatches
strResult = strResult & " " & objMatch.Value
Next objMatch
End If

GetJobNum = LTrim(strResult)

Set objRegEx = Nothing

End Function

'---------------------------------------------

If you string was in cell a1, then
=GetJobNum(a1)
would return
07-021

Steve Yandl



"jorlypong" wrote in message
...
I am learning some vba and am trying to delete some useless info both
before
and after my needed info (job #)

ex. "hello:07-021 goodbye"

I need to have the "hello:" and " goodbye" leaving only the job number.
Note: all the job numbers start with two digits, have a hyphen, and end
with
three digits such as "00-000"

Any help would be great . . . thank you

Just what the doctor ordered. Thanks for looking and helping