How to find and replace with regex in excel

30

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?

This question is tagged with regex excel replace

~ Asked on 2016-01-15 20:45:01

The Best Answer is


46

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

~ Answered on 2017-06-22 17:53:11


16

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 on 2016-01-15 23:26:45


Most Viewed Questions: