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
rwerwI 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?
65 Answers
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
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.
1As 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 Subwill produce:
Now is 2021 year, you can use Excel's Replace
- Key point:
- Find:
texts are * - Replace:
texts are replaced
- Find:
- Steps
- select content to replace, (upper right corner) choose replace
- in replace popup window, input rule for
FindandReplace - click
Replace All, done ^_^
- select content to replace, (upper right corner) choose replace
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.
3
