'How to find and replace with regex in excel

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?

regexexcelreplace


Solution 1:[1]

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

Solution 2:[2]

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.

Solution 3:[3]

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
      • enter image description here
    • in replace popup window, input rule for Find and Replace
      • enter image description here
    • click Replace All, done ^_^
      • enter image description here

Solution 4:[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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 gt6989b
Solution 2 Scott Craner
Solution 3 crifan
Solution 4 Hammad Khan

Related Questions