36

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?

user5796570
  • 363
  • 1
  • 3
  • 4
  • 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
  • 1
    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
  • 6
    @AdamAxtmann That article is for Word and doesn't work on Excel 2016. – not2qubit Dec 14 '17 at 08:36

5 Answers5

52

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
gt6989b
  • 3,937
  • 8
  • 40
  • 61
Crag
  • 1,509
  • 13
  • 30
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.

Scott Craner
  • 137,650
  • 9
  • 45
  • 77
  • 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
5

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:

enter image description here

Gary's Student
  • 94,018
  • 8
  • 54
  • 89
4

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
crifan
  • 10,971
  • 1
  • 61
  • 46
1

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.

Hammad Khan
  • 15,376
  • 14
  • 106
  • 131
  • 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. – Hammad Khan 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