52

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?

Improve this question
7
  • See Office support support.office.com/en-us/article/… Commented Jan 15, 2016 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? Commented Jan 15, 2016 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 Commented Jan 15, 2016 at 20:53
  • please refer answer in this question! stackoverflow.com/questions/22542834/… Commented Nov 13, 2017 at 14:13
  • 7
    @AdamAxtmann That article is for Word and doesn't work on Excel 2016. Commented Dec 14, 2017 at 8:36

12 Answers 12

Reset to default
66

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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Alternatively, you can use LibreOffice, which supports regular expressions as well.
questions clearly says "How can I do that in Excel?".
Too slow for my use case. Page froze for a minute or more for data of size 15,000 rows by 9 columns
@Mohsen i think the answer is it can't be done in Excel with Regex
This is the most stackoverflow answer ever. I keep coming back just to make sure it really exists
|
24

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.

Improve this answer

4 Comments

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)
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.
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 =
Notable that this is in the help, but nobody saw it. :-)
23

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.

Improve this answer

1 Comment

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

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
Improve this answer

4 Comments

Mixing two different languages in your answer is detrimental to the reader.
are you serios? )) would you like an anwser with arab letters? :))
Is the function described by the images part of Excel or an addon? It would be useful if someone could replace the images with images containing text in English.
It works! The screenshot is just using the regular Search & Replace dialog box. If you search for something * and replace with else, Excel will automatically and always also capture what it matched with the wildcard and that captured text alone will be replaced with what you put in the replacement box. Which is at least usually what you want, I suppose, haha. So this example would replace something new with something else.
8

You can...

  1. Exit Excel
  2. Make a copy of your Excel file and add to the extension (i.e. .xls or .xlsm) .xml.zip. For example, test.xls becomes test.xls.xml.zip.
  3. Unzip the .zip file. Yes, Excel files are really .zip files.

This will give you a directory named test.xls.xml (or test.xlsm.xml) that contains the following directory structure: \xl\worksheets.

In the worksheets directory are your Excel worksheets, in XML format. They are not formatted for readability, so use an editor that is capable of "pretty" formatting the XML (I use EditPlus or XMLSpy. There are many out there.). Now that you can read the XML, you can use the regular expression feature of the editor to make changes.

When you are done, go to the test.xls.xml (or test.xlsm.xml) directory. You will see the _rels, docProps and xl subdirectories and the [Content_Types].xml file. Select everything, right click and choose Send to > Compressed (.zip) folder. Make sure there are no .bak files left over by your text editor (in the worksheets subdirectory). If they're there, delete them before creating the new .zip file. Now rename the extension of this new .zip file to .xls (or .xlsm).

You can now open this file in Excel. Excel won't mind that you've "pretty" formatted the XML.

I have done this exact procedure many times, never with any problems (unless I accidently left the .bak files laying around).

One word of warning, Excel cells that contain just text (i.e. no formulas) store the text in a different file and reference them from within the worksheet file, so you won't see that text there. Any text contained in a formula (i.e. CONCAT("ABC", "DEF")) is preserved in the worksheet.

Improve this answer

2 Comments

Extremely Surgical but works!
- This worked like a charm. I used 7zip to unzip, Notepad++ to find and fix the strings I needed to replace. I my case I had to remove leading and trailing APOSTROPHE chars from every cell. That text turned out to be in the "xl/sharedstrings.xml" file. I then dropped the fixed file back into the zip and gave it a new XLSX name. Perfecto!
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

Improve this answer

Comments

0

Regex Replace in Excel can be achieved by using self defined worksheetfunctions. Below an exampe VBA code for RegexReplace.

========

Option Explicit
Private oRegex As Object

Public Function RegexReplace(ByVal strText As String, ByVal strPattern As String, ByVal strReplace As String, _
    Optional ByVal strAltText As Variant = Null, Optional ByVal IgnoreCase As Boolean = False) As String

    If oRegex Is Nothing Then
        Set oRegex = CreateObject("VBScript.RegExp")
        With oRegex
            .Global = True
            .MultiLine = True
        End With
    End If
    
    RegexReplace = VBA.IIf(VBA.IsNull(strAltText), strText, strAltText)
    On Error GoTo Go_Err:
    If strPattern <> "" Then
        With oRegex
            .IgnoreCase = IgnoreCase
            .Pattern = strPattern
            
            If .Test(strText) Then
                    RegexReplace = .Replace(strText, strReplace)
            End If
        End With
    End If
            
    Go_Err:
End Function

======

Used in a worksheet cell: "=RegexReplace(Original_Text, Regex_search, Regex_replace, Alternative_text_optional, Case_sensitive_optional)".

Improve this answer

Comments

0

If you're using the latest version of mac or windows and you are using the desktop version of Excel, you might be able to use the new REGEXREPLACE function. If you're on web Office 365 like me, you may have to use something like the beast below:

=IF(ISERROR(SEARCH(",*", J3,1)),J3,REPLACE(J3,SEARCH(",*",J3),LEN(J3)-SEARCH(",*",J3),""))

In this snippet, I am trying to replace ",.*$" with "". In short, If there's a comma in the field, I wish to remove the comma and everything after the comma. I did this with REGEXREPLACE in Google Sheets.

In Excel, I had to get tricky.

There's a REPLACE function that can take text from one field and replace it with other text, but it's bare-bones. You have to specify the index and length of the substring to replace, along with the string with which to replace it.

There's also a SEARCH function, which doesn't do regexes but does do globbing, which is close and good enough for me. It returns the start index of any matches to the pattern if such a match exists, and an error if it does not.

So I had to use ISERROR to see if search found anything. If it didn't, I just return the contents of the field which I wish to change. If it did find something, I:

  • Call SEARCH("<PATTERN>",<FIELD>) to get the index of the match
  • Use that value to specify what index to start replacing text for REPLACE
  • Use that value to specify what index to end replacing text for REPLACE by calculating LEN(<FIELD>)-SEARCH(...)
  • Specify the replacement string
  • Put all that stuff in a REPLACE function

I know it's involved, but hopefully helps some poor searching soul in the future.

Improve this answer

Comments

0

You can also use Notepad++ to find or replace for example all special characters with the regular expression: [^a-zA-Z0-9\s].

I hope it can help.

enter image description here

Improve this answer

Comments

0

Excel supports the REGEXREPLACE function:

=REGEXREPLACE(A1, "^(texts are ).*", "\1replaced")

Demonstration of the functionality working in Excel

This feature is available starting with recent builds of Excel.

These functions are currently available to Beta Channel users running:

  • Windows: Version 2406 (Build 17715.20000) or later
  • Mac: Version 16.86 (Build 24051422) or later
Improve this answer

Comments

0

Excel for Microsoft 365 supports now the use Regex natively with three function

REGEXEXTRACT, REGEXREPLACE, REGEXTEST

For your case you can use:

=REGEXREPLACE(A1,"(texts are).*","$1 replaced")

You can refer to Microsoft support for the documentation: https://support.microsoft.com/en-us/office/regexreplace-function-9c030bb2-5e47-4efc-bad5-4582d7100897

Improve this answer

Comments

0

I do this with Notepad++
(Note this method works regardless of the version of Excel/Office you have.)

  1. Save your work using File => "Save As" and select "CSV-UTF-8" as the file save type.
    (This is important, especially if your spreadsheet has non-English characters.)

  2. Open the .csv file in Notepad++

  3. Have fun with your regex replacements and save when done.

  4. Select the saved .csv file and "Open With" Excel.
    You might have to adjust the column widths and/or row heights, but it's much faster to do it that way than in Excel.

(Side note:)
One of the things I do with my data-conversion spreadsheets is, (after I get the data reasonably normalized, (like data in the same columns, etc.), I create a new first column and use the shift-drag feature to put an "index" in the first column starting with 1 so that I can keep track of the lines, and re-sort them if needed. (My European bank statements come sorted latest to earliest and my financial software wants things sorted earliest to latest.)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.