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?

See Office support https://support.office.com/en-us/article/Find-and-replace-text-by-using-regular-expressions-Advanced-eeaa03b0-e9f3-4921-b1e8-85b0ad1c427f — Adam Axtmann, Jan 15 '16 at 20:47
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? — user5796570, Jan 15 '16 at 20:49
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 — Adam Axtmann, Jan 15 '16 at 20:53
please refer answer in this question! https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — Luke, Nov 13 '17 at 14:13
@user5796570 , how do I use this? Do I copy paste that code above to a cell as a formula with an `=` equals to sign? — YouHaveaBigEgo, Dec 04 '17 at 20:04
@AdamAxtmann That article is for Word and doesn't work on Excel 2016. — not2qubit, Dec 14 '17 at 08:36
Not really regex, but this reply below, https://stackoverflow.com/a/34819759/6266107 , answer your question. — Antonio, Aug 25 '23 at 08:31

score 60 · Answer 1 · edited May 14 '18 at 17:43

60

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:

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

edited May 14 '18 at 17:43

gt6989b

4,125
8
46
64

answered Jun 22 '17 at 17:53

Crag

1,723
17
35

17

Alternatively, you can use LibreOffice, which supports regular expressions as well. – Luca Oct 03 '17 at 08:50
35

questions clearly says "How can I do that in Excel?". – Mohsen Feb 07 '20 at 14:08
2

Too slow for my use case. Page froze for a minute or more for data of size 15,000 rows by 9 columns – Colm Bhandal Mar 01 '21 at 13:56
@Mohsen i think the answer is it can't be done in Excel with Regex – Leon Mar 16 '22 at 04:46

score 19 · Answer 2 · answered Jan 15 '16 at 23:26

19

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.

answered Jan 15 '16 at 23:26

Scott Craner

148,073
10
49
81

6

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. – mickmackusa Sep 05 '21 at 22:49

score 9 · Answer 3 · answered May 27 '21 at 03:17

9

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 ^_^

answered May 27 '21 at 03:17

crifan

12,947
1
71
56

23

Mixing two different languages in your answer is detrimental to the reader. – Pearce Dec 28 '21 at 21:41
3

Thank you for the Chinese screenshots :) – TheTechGuy Feb 02 '22 at 18:18
are you serios? )) would you like an anwser with arab letters? :)) – serge Feb 23 '23 at 09:33

score 7 · Answer 4 · answered Feb 02 '22 at 18:32

7

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.

answered Feb 02 '22 at 18:32

TheTechGuy

16,560
16
115
136

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) – Jon Grah Mar 22 '22 at 07:51
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. – TheTechGuy Mar 23 '22 at 13:24
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 `=` – Jon Grah Mar 23 '22 at 13:38
Notable that this is in the help, but nobody saw it. :-) – Antonio Aug 25 '23 at 08:32

score 5 · Accepted Answer · answered Jan 15 '16 at 20:59

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:

mbmast · Answer 6 · 2023-04-13T00:33:45.597

You can...

Exit Excel
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.
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.

Extremely Surgical but works! – mohsyn Jul 12 '23 at 04:11 — mohsyn, Jul 12 '23 at 04:11

How to find and replace with regex in excel

6 Answers6