How to find and replace with regex in excel

Question

I have an excel file with 1 column and multiple rows.

The rows contain various text, here's an example:

texts are home
texts are whatever
dafds
dgretwer
werweerqwr
texts are 21412
texts are 346345
texts are rwefdg
terfesfasd
rwerw

I want to replace "texts are *" where * is anything after "texts are" with a specific word, for example "texts are replaced". How can I do that in Excel?

Mutualism

See Office support support.office.com/en-us/article/… – Foothill

I've read that article and I've tried a few of the examples and also making my own regex, but nothing gave any results. Am I doing something wrong? – Mutualism

What version of Excel are you using? I have 2016, I just followed the steps in the "Wildcards for items you want to replace" section, using your example above, and was able to find "texts are *" and replace all instances with "texts are replaced" and it worked as expected, unless I'm misunderstanding – Foothill

please refer answer in this question! #22543334 – Cazares

@Mutualism , how do I use this? Do I copy paste that code above to a cell as a formula with an = equals to sign? – Foulup

@AdamAxtmann That article is for Word and doesn't work on Excel 2016. – Masuria

Not really regex, but this reply below, https://mcmap.net/q/352608/-how-to-find-and-replace-with-regex-in-excel , answer your question. – Fadeout

Answer 1

As an alternative to Regex, running:

Sub Replacer()
   Dim N As Long, i As Long
   N = Cells(Rows.Count, "A").End(xlUp).Row

   For i = 1 To N
      If Left(Cells(i, "A").Value, 9) = "texts are" Then
         Cells(i, "A").Value = "texts are replaced"
      End If
   Next i
End Sub

will produce:

Dorindadorine

Answer 2

Use Google Sheets instead of Excel - this feature is built in, so you can use regex right from the find and replace dialog.

To answer your question:

1. Copy the data from Excel and paste into Google Sheets
2. Use the find and replace dialog with regex
3. Copy the data from Google Sheets and paste back into Excel

Everyman

Alternatively, you can use LibreOffice, which supports regular expressions as well. – Cribbs

questions clearly says "How can I do that in Excel?". – Furore

Too slow for my use case. Page froze for a minute or more for data of size 15,000 rows by 9 columns – Jadejaded

@Furore i think the answer is it can't be done in Excel with Regex – Springhouse

This is the most stackoverflow answer ever. I keep coming back just to make sure it really exists – Rakel

Answer 3

If you want a formula to do it then:

=IF(ISNUMBER(SEARCH("*texts are *",A1)),LEFT(A1,FIND("texts are ",A1) + 9) & "WORD",A1)

This will do it. Change `"WORD" To the word you want.

Lumpfish

This answer is missing its educational explanation. Please edit this answer to explain how this expression works. I'm sure this answer is "self-explanatory" for people that work regularly with excel, but for the non-avid user, this pile of commands will likely be incomprehensible. – Mucous

Answer 4

Apparently, Excel does not use Regex, but there is a workaround. You can use *, ? or ~ in your search pattern.

To find

? (question mark) =  Any single character. For example, sm?th finds "smith" and "smyth"  

* (asterisk) = Any number of characters For example, *east finds "Northeast" and "Southeast"  

~ (tilde) followed by ?, *, or ~ = A question mark, asterisk, or tilde. For example, fy06~? finds "fy06?"

you can use these combinations to get a similar pattern that will be close to a regex expression.

Goodard

can you limit the number of characters? ? finds a single character and * finds unlimited characters. what if you want to find unlimited characters? Also, how do you use grouping to refill some of the characters found in the find (like in regex) – Cayes

You can use ?? to replace two characters and ???? to replace four. For unlimited characters you use * but this essentially matches everything till the end of string. – Goodard

Other users were correct. Excel is ill-equipped to do regex search properly. Better to copy formulas to external txt editor like notepad++ (find/replace = to /=; copy formulas to notepad++) and then make all your find/replace/regex changes there (keeping the tab separators intact). Then copy everything back into excel and find/replace /= back to = – Cayes

Notable that this is in the help, but nobody saw it. :-) – Fadeout

Answer 5

Now is 2021 year, you can use Excel's Replace

• Key point:
  • Find: texts are *
  • Replace: texts are replaced
• Steps
  • select content to replace, (upper right corner) choose replace
  • in replace popup window, input rule for Find and Replace
  • click Replace All, done ^_^

Codling

Mixing two different languages in your answer is detrimental to the reader. – Britannic

Thank you for the Chinese screenshots :) – Goodard

are you serios? )) would you like an anwser with arab letters? :)) – Raab

Answer 6

You can...

1. Exit Excel
2. Make a copy of your Excel file and add to the extension (i.e. .xls or .xlsm) .xml.zip. For example, test.xls becomes test.xls.xml.zip.
3. Unzip the .zip file. Yes, Excel files are really .zip files.

This will give you a directory named test.xls.xml (or test.xlsm.xml) that contains the following directory structure: \xl\worksheets.

In the worksheets directory are your Excel worksheets, in XML format. They are not formatted for readability, so use an editor that is capable of "pretty" formatting the XML (I use EditPlus or XMLSpy. There are many out there.). Now that you can read the XML, you can use the regular expression feature of the editor to make changes.

When you are done, go to the test.xls.xml (or test.xlsm.xml) directory. You will see the _rels, docProps and xl subdirectories and the [Content_Types].xml file. Select everything, right click and choose Send to > Compressed (.zip) folder. Make sure there are no .bak files left over by your text editor (in the worksheets subdirectory). If they're there, delete them before creating the new .zip file. Now rename the extension of this new .zip file to .xls (or .xlsm).

You can now open this file in Excel. Excel won't mind that you've "pretty" formatted the XML.

I have done this exact procedure many times, never with any problems (unless I accidently left the .bak files laying around).

One word of warning, Excel cells that contain just text (i.e. no formulas) store the text in a different file and reference them from within the worksheet file, so you won't see that text there. Any text contained in a formula (i.e. CONCAT("ABC", "DEF")) is preserved in the worksheet.

Sawhorse

Extremely Surgical but works! – Fondness

Answer 7

Regex Replace in Excel can be achieved by using self defined worksheetfunctions. Below an exampe VBA code for RegexReplace.

========

Option Explicit
Private oRegex As Object

Public Function RegexReplace(ByVal strText As String, ByVal strPattern As String, ByVal strReplace As String, _
    Optional ByVal strAltText As Variant = Null, Optional ByVal IgnoreCase As Boolean = False) As String

    If oRegex Is Nothing Then
        Set oRegex = CreateObject("VBScript.RegExp")
        With oRegex
            .Global = True
            .MultiLine = True
        End With
    End If
    
    RegexReplace = VBA.IIf(VBA.IsNull(strAltText), strText, strAltText)
    On Error GoTo Go_Err:
    If strPattern <> "" Then
        With oRegex
            .IgnoreCase = IgnoreCase
            .Pattern = strPattern
            
            If .Test(strText) Then
                    RegexReplace = .Replace(strText, strReplace)
            End If
        End With
    End If
            
    Go_Err:
End Function

======

Used in a worksheet cell: "=RegexReplace(Original_Text, Regex_search, Regex_replace, Alternative_text_optional, Case_sensitive_optional)".

Tetracycline

Answer 8

If you're using the latest version of mac or windows and you are using the desktop version of Excel, you might be able to use the new REGEXREPLACE function. If you're on web Office 365 like me, you may have to use something like the beast below:

=IF(ISERROR(SEARCH(",*", J3,1)),J3,REPLACE(J3,SEARCH(",*",J3),LEN(J3)-SEARCH(",*",J3),""))

In this snippet, I am trying to replace ",.*$" with "". In short, If there's a comma in the field, I wish to remove the comma and everything after the comma. I did this with REGEXREPLACE in Google Sheets.

In Excel, I had to get tricky.

There's a REPLACE function that can take text from one field and replace it with other text, but it's bare-bones. You have to specify the index and length of the substring to replace, along with the string with which to replace it.

There's also a SEARCH function, which doesn't do regexes but does do globbing, which is close and good enough for me. It returns the start index of any matches to the pattern if such a match exists, and an error if it does not.

So I had to use ISERROR to see if search found anything. If it didn't, I just return the contents of the field which I wish to change. If it did find something, I:

• Call SEARCH("<PATTERN>",<FIELD>) to get the index of the match
• Use that value to specify what index to start replacing text for REPLACE
• Use that value to specify what index to end replacing text for REPLACE by calculating LEN(<FIELD>)-SEARCH(...)
• Specify the replacement string
• Put all that stuff in a REPLACE function

I know it's involved, but hopefully helps some poor searching soul in the future.

Guggle