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

Question

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

If you have tried using VBA, please post the code you tried. — Tim Williams, Oct 04 '17 at 18:44
Do you have a pattern like First character is A and number, or are you finding many exact matches? If it is the late option, you won't need [ReGex](https://regex101.com/r/paE6u4/1). And should the output of `101878` contain `FA`? — danieltakeshi, Oct 04 '17 at 18:53
ctwheels, what language is that for? I could not get that to work in Excels Find/Replace window. — Michael Kapp, Oct 04 '17 at 20:56
@MichaelKapp Refer to [this](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) to program Regex on Excel — danieltakeshi, Oct 04 '17 at 20:59
Everything I need to replace starts with "A" followed by 6 digits. These correspond to our issues in a different tracking tool. I need to import the data from excel, but the import is failing, because the Issues need to be followed by " FA". This is the reason for why I need this find / replace. Would like to know in general as well how to find and replace portions of strings. Seems like something good to know. — Michael Kapp, Oct 04 '17 at 20:59
@MichaelKapp VBA. I followed the information here: https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops (which is the same link posted by daniel) and https://stackoverflow.com/questions/11867580/vba-excel-regex-b-word-boundary-doesnt-match-if-word-is-at-beginning-of-stri for word boundary — ctwheels, Oct 04 '17 at 21:16
You will need regexp, or you will can use VBA to search the cell contents and do the replace without regexp. There's limited support for wildcards in the Excel built-in Search/Replace dialog, and no support for RegExp. — Tim Williams, Oct 05 '17 at 02:18
@MichaelKapp What is confusing is that `101878` don't start with A and still needs `FA` to be added. Your example is confusing and don't match your comment description of the problem. And [here](https://stackoverflow.com/a/45437646/7690982) is a great example on how to use ReGex (the one ctwheels wrote) or how to use LIKE with wildcards. — danieltakeshi, Oct 05 '17 at 13:29
@danieltakeshi Let me be more clear. In my real life issue, there are cases where there is no letter "A" preceding the 6 digit issue number. As far as just getting a simple answer here on Stackoverflow, I don't care of someone posts a solution of how to handle the letter "A" or not. There are not many cases where the letter "A" is missing. — Michael Kapp, Oct 05 '17 at 19:46
By [Alt+Enter] do you mean split across separate lines within a cell with Chr(10) ? And is it always a capital A? — QHarr, Jun 04 '18 at 08:18

score 1 · Answer 1 · answered Jun 03 '18 at 22:59

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

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

1 Answers1