Excel WildCard Find and Replace

SportingFun

Hi

I have a series of strings in a column in the below format the alpha and numeric values are different in each one.

The data looks like ABCDEF;#45678;#GHIJKL;#12345 (no spaces) and I wish to replace the #45678 digit sequence keeping the alpha characters however any kind of wildcard search replace I have attempted so far removes the second set of alpha characters.

Has anybody come across this before or have any suggestions please?

Thanks

Pelvis

Are you trying to do this with a formula?

It works fine using the CTRL+F > Replace in my excel.

SportingFun

Pelvis wrote: »

Are you trying to do this with a formula?

It works fine using the CTRL+F > Replace in my excel.

In my excel using 2013 with the CTRL + F > Replace using wildcard search #* it replaces the second string each time perhaps my wildcard option is incorrect?

pillphil

Just to be sure I understand your requirements,

The below code will replace the character sequence #<any amount of digits only>; in a cell set range with a determined replacement

so if I have an excel with ABCDEF;#45678;#GHIJKL;#12345 in cell A1 and provide it with #zz7711; and a range of A1:A1 it will replace the contents of A1 with ABCDEF;#zz7711;#GHIJKL;#12345

Correct?

Source code from:
https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops

Follow the instructions in step 1 in the link to enable developer tab and add regex

then
Return to excel, click macros beside the visual basic button
Put a name in macro name and click create
Paste in this code

Private Sub simpleRegex()

    Dim strPattern As String: strPattern = "#([0-9])+;" ' #1 - this finds anything in the format #<any amount of digits>;
    Dim strReplace As String: strReplace = "alternative goes here" ' #2 - chnage this to the required replacement
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A1") ' #3 - change this to the desired range

    For Each cell In Myrange
        If strPattern <> "" Then
            strInput = cell.Value

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

            If regEx.Test(strInput) Then
                cell.Value = regEx.Replace(strInput, strReplace)
            Else
                MsgBox ("Not matched")
            End If
        End If
    Next
End Sub

change "alternative goes here" on the line with #2 to the desired string (keep the "")
change the range "A1:A1" on the line with #3 to the range you want to change (keep the "")

Hit the green arrow and it should change the ;<digits># part of every cell in the range (#3 in code) to the replacement value (#2 in code)

Turtwig

SportingFun wrote: »

Hi

I have a series of strings in a column in the below format the alpha and numeric values are different in each one.

The data looks like ABCDEF;#45678;#GHIJKL;#12345 (no spaces) and I wish to replace the #45678 digit sequence keeping the alpha characters however any kind of wildcard search replace I have attempted so far removes the second set of alpha characters.

Has anybody come across this before or have any suggestions please?

Thanks

Would the text to columns feature on the entire column not work? Set a custom delimiter, ";" and then edit your columns that you need to replace the substrings with what ever string you want. Finally, concatenate them all back into the one string again if that's what you need?

Alternatively, there's also the possibility of using the substitute formula, though that is imo a fair messier if you aren't sticking consistently with named ranges.

SportingFun

pillphil wrote: »

Just to be sure I understand your requirements,

The below code will replace the character sequence #<any amount of digits only>; in a cell set range with a determined replacement

so if I have an excel with ABCDEF;#45678;#GHIJKL;#12345 in cell A1 and provide it with #zz7711; and a range of A1:A1 it will replace the contents of A1 with ABCDEF;#zz7711;#GHIJKL;#12345

Correct?

Source code from:
https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops

Follow the instructions in step 1 in the link to enable developer tab and add regex

then
Return to excel, click macros beside the visual basic button
Put a name in macro name and click create
Paste in this code

Private Sub simpleRegex()

    Dim strPattern As String: strPattern = "#([0-9])+;" ' #1 - this finds anything in the format #<any amount of digits>;
    Dim strReplace As String: strReplace = "alternative goes here" ' #2 - chnage this to the required replacement
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A1") ' #3 - change this to the desired range

    For Each cell In Myrange
        If strPattern <> "" Then
            strInput = cell.Value

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

            If regEx.Test(strInput) Then
                cell.Value = regEx.Replace(strInput, strReplace)
            Else
                MsgBox ("Not matched")
            End If
        End If
    Next
End Sub

change "alternative goes here" on the line with #2 to the desired string (keep the "")
change the range "A1:A1" on the line with #3 to the range you want to change (keep the "")

Hit the green arrow and it should change the ;<digits># part of every cell in the range (#3 in code) to the replacement value (#2 in code)

Thank you for the help this code has gotten me a lot closer to the final goal by using this code I am getting from ABCDEF;#45678;#GHIJKL;#12345 to ABCDEF;#GHIJKL;#12345 so getting closer
Help much appreciated

SportingFun

Turtwig wrote: »

Would the text to columns feature on the entire column not work? Set a custom delimiter, ";" and then edit your columns that you need to replace the substrings with what ever string you want. Finally, concatenate them all back into the one string again if that's what you need?

Alternatively, there's also the possibility of using the substitute formula, though that is imo a fair messier if you aren't sticking consistently with named ranges.

The custom delimiter would work expect I have other columns in my spread sheet that I am working with as well so I don't want to go down this route but thanks for the suggestion

pillphil

SportingFun wrote: »

Thank you for the help this code has gotten me a lot closer to the final goal by using this code I am getting from ABCDEF;#45678;#GHIJKL;#12345 to ABCDEF;#GHIJKL;#12345 so getting closer
Help much appreciated

Did you supply the replacement string in the line with #2?

the result you have is what I would expect if you used this line:

Dim strReplace As String: strReplace = ""

You would need to do this, for example

Dim strReplace As String: strReplace = "#zz7711;"

namenotavailablE

If a 'temporary helper column' can be inserted to the right of the old text and assuming that your 'old text' is in column A1, this would also do what you're looking for (at the end of the formula, I'm using the replacement text as "#99999" so change that bit as required):

=SUBSTITUTE(A1,MID(A1,FIND(";",A1)+1,FIND(";",A1,FIND(";",A1))-1),"#99999")

You could then copy the new result and paste values over the old results then delete the temporary helper column.