Need to edit string in cell
 

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

Need to edit string in cell
 

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

Need to edit string in cell
 

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

Thank You
 

Just what the doctor ordered. Thanks for looking and helping