How to use Excel VBA regex in-cell function to move parts of a string

Question

Edits to my original post.

New to using VBA in Excel as well as regex. I think regex offers the best solution for me due to the wide variability in the strings.
I would really appreciate any help - not looking for handouts - just want to learn.

I have thousands of text entries that look like the following in my workbook (one column):

911407 - Ariens 34" Wide-Area Walk-Behind Mower, 10.5hp Briggs & Stratton, Gear, CARB (SN: 000101 & Above)
911379 (LMSPE CE) - Ariens Razor 21" Self-Propelled Lawn Mower, 175cc Subaru (SN: 020347 - 025999)
08200835 (CE) - Ariens CE AX 136cc Recoil Engine
922013 (ST4) - Ariens Snow Blower, CE 4hp Tecumseh (SN: 000101 & Above)

I want to use an Excel VBA in-cell function (macro would also be nice, but I have to start somewhere) to:

• Find each occurrence of the sub-string " (CE) " or "CE" or "CE" contained in a multi-character parenthetic expression
• Remove it from its current position
• Edit it (to strip off the leading space, strip off the opening/closing parantheses, add a comma)
• Move the fresh edit to a new position in the string, at a location that is right before the "(SN:...)"

The desired results, using the above cited entries are:

911407 - Ariens 34" Wide-Area Walk-Behind Mower, 10.5hp Briggs & Stratton, Gear, CARB (SN: 000101 & Above)
911373 (LMP) - Ariens Razor 21" Push Lawn Mower, 175cc Subaru, CE (SN: 035000 - 054999)
08200835  - Ariens CE AX 136cc Recoil Engine, CE
922013 (ST4) - Ariens Snow Blower, 4hp Tecumseh, CE (SN: 000101 & Above)

This is what I have created so far that finds the " (CE) " and removes it. I only got this far by reading past posts and experimenting.

Function TomCEregex(Myrange As Range) As String
Dim regEx As New RegExp
Dim strPattern As String
Dim strInput As String
Dim strReplace As String

strPattern = " \(CE\)"

If strPattern <> "" Then
    strInput = Myrange.Value
    strReplace = ""

    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = strPattern
    End With

    If regEx.Test(strInput) Then
        TomCEregex = regEx.Replace(strInput, strReplace)
    Else
        TomCEregex = "Not Matched"
    End If
End If

End Function

Obviously, I am not even close, but I hope you see that I am trying.

Thanks.

Answer 1

No need for RegEx here or VBA, when SUBSTITUTE function will do the trick:

=SUBSTITUTE(SUBSTITUTE(A1," (CE) ","")," (",", CE (")