CODEWITHSUNDEEP
regexexcelvbareplace
Michael Kapp
Michael Kapp

Reputation: 97

How to use Regex in excel to find/replace the end of a string

I am looking to do a quick find replace in Excel (or VBA) to Find strings in specific format and append some text at the end.

Example: Find: A123456

Replace with: A123456 FA

I know my first character is "A" followed by 6 digits. I just need to append a space and a few characters like " FA" in my example above.

Some cells contain multiple strings separated with either a space or [Alt+Enters]. Ex. "A123456 [Alt+Enter]A987654"

Ex: Find: A123456[Alt+Enter]A987654[Alt+Enter]101878

Replace: A123456 FA[Alt+Enter]A987654 FA[Alt+Enter]101878 FA

I tried random configurations of ,?,<>,~*,~?, but I don't see what will work. I did not find what I need for this issue, but seemed most useful. https://support.office.com/en-us/article/Find-and-replace-text-and-other-data-in-a-Word-document-c6728c16-469e-43cd-afe4-7708c6c779b7?ui=en-US&rs=en-US&ad=US#ex1

Upvotes: 1

Views: 8953

Answers (1)

learnAsWeGo
learnAsWeGo

Reputation: 2282

Realize that this is an old question, but have been practicing with regex a bit and used this problem as practice. I think it will accomplish what you would like. Replace the string that I reference with the cell values/range that holds the strings that you want to test. Instead of printing swap cell value with item + " FA" ie cells(1,1) = item + " FA"

Sub NewNew()

    Dim reg As RegExp
    Set reg = New RegExp

        reg.Pattern = "\b([aA\d]?\d{6})"
        reg.Global = True

    Dim arr As Variant

    Dim str As String
        str = "123456 A1456 B156221 A742121 C654123 A123456 D123456 E876543"

    Set arr = reg.Execute(str)

    For Each Item In arr
        Debug.Print Item + " FA"
    Next Item

End Sub

Upvotes: 1

Related Questions