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?
The answer is
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:
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.
Source: Stackoverflow.com