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?

Answer 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

Answer 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.

Answer 3

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: