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.

So why not just remove " (CE)" from strings that contain it then always place ", CE " in front of occurrences of "(SN:"? — dwirony
– dwirony, Commented Dec 20, 2017 at 20:27

Scott Holtzman · Accepted Answer · 2017-12-20 20:29:43Z

2

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

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

answered Dec 20, 2017 at 20:29

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Tom F Over a year ago

It is much simpler, however, I have thousands of entries (rows) and of course not each entry contains the target string pattern. Thus, as I understand it, I would have to evaluate each entry first to see if it contains the string pattern and then implement the SUBSTITUTE function on only those instances. If I use a regex based in-cell function than I wouldn't have to do the row by row evaluation. If the target string pattern was not found, the function would return the original value. Or maybe there is an IF function I can combine with the SUBSTITUTE function to take care of it.

Scott Holtzman Over a year ago

Not necessarily. If I understand you correctly, the nature of Substitute will solve that issue as it will simply leave what is there if the pattern match does not exist so you can drag formula down rows and it should work if it finds the pattern or not.

Tom F Over a year ago

I tried the SUBSTITUTE function on cells that did not contain the target pattern, but it returned the original value with the added target pattern when it wasn't desired.

Scott Holtzman Over a year ago

I think I may know what is happening, but you do not give any examples of what the other pattern(s) look like. You can use an IF formula if need be. If you have a question about how to handle the other cases with this formula post another question.

Tom F Over a year ago

My apologies if my original question did not provide adequate info. I will rephrase and repost.

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related