How to Replace Text in Excel: Find & Replace, SUBSTITUTE, REPLACE & Power Query

Learning how to replace text in excel ranks among the most useful skills a spreadsheet user can pick up, and it is one of those rare features that pays for itself within minutes of being learned.

Whether you imported a CSV with stray dollar signs in front of every number, inherited a workbook where every product code starts with the wrong prefix, or simply need to anonymise a list of names before sharing the file with a colleague, the right replacement technique will save you hours of manual cell-by-cell editing. Excel ships with four distinct ways to swap one string of text for another, and each one shines in a different context.

The first method, and the one most people reach for instinctively, is the classic Find and Replace dialog you summon with Ctrl plus H on Windows or Cmd plus H on a Mac. It is fast, interactive, and supports wildcards and scope controls. The second method is the SUBSTITUTE function, a formula-based approach that lets you embed replacement logic directly inside other calculations.

The third is the REPLACE function, which works on character positions rather than matching patterns. The fourth, and most powerful, is Power Query Replace Values, which handles table-wide transformations and can be refreshed every time your source data updates.

Choosing between them is rarely an either-or decision. A seasoned analyst will use Find and Replace for a quick one-off cleanup, SUBSTITUTE inside a formula when the original data must remain untouched, REPLACE when the replacement happens at a known position regardless of content, and Power Query when the same transformation needs to run on every refresh.

By the end of this guide you will know which tool to grab without thinking, and your spreadsheets will become significantly cleaner and easier to maintain. The same logic applies whether you work in Excel text functions daily or only open a workbook every few weeks.

We will walk through every method in order, starting with the dialog box and moving up the complexity ladder. Along the way we will cover wildcards, line break replacement, nested SUBSTITUTE chains, bulk workbook-wide replacements, and the limitations you should know about before trusting a replace operation on production data. There is no universal best answer, so the goal here is to give you a clear mental model of when to pick each technique.

A note on platforms before we begin. The Find and Replace dialog behaves almost identically in Excel for Windows, Excel for Mac, and Excel for the Web, though the keyboard shortcut differs and a few advanced options like format-based search are missing in the web version. SUBSTITUTE and REPLACE are pure functions and work identically everywhere, including Google Sheets. Power Query is available in Excel for Windows and Microsoft 365 on Mac, but the Power Query editor on Mac still lags slightly behind the Windows version in terms of available transformations.

If you have never explored the Excel Power Query editor before, the section on Replace Values will introduce it gently. And if you are comfortable with formulas already, you can jump straight to the SUBSTITUTE and REPLACE deep dives further down. Either way, every example in this article uses real data patterns you will encounter in actual office work rather than contrived puzzles.

The Find and Replace dialog is the workhorse of text replacement in Excel and the first stop for any quick edit. Press Ctrl plus H on Windows or Cmd plus H on a Mac and the dialog appears with two fields: Find what and Replace with.

Type the string you want to find in the first field, the replacement in the second, and decide whether to click Replace to step through matches one at a time or Replace All to swap every occurrence at once. The status bar at the bottom of the dialog reports how many replacements were made, which is your immediate sanity check.

What makes the dialog genuinely powerful is the Options panel, which expands when you click the Options button. Inside you will find Match case to enforce case sensitivity, Match entire cell contents to limit hits to cells whose entire value equals the search string, and a Within dropdown that controls the scope of the operation.

Within set to Sheet limits replacements to the active worksheet, while Within set to Workbook applies the same operation across every sheet in the file. The Search dropdown lets you choose By Rows or By Columns, which mostly affects the order of stepwise replacements rather than the final outcome.

The Look in dropdown is another underused gem. Set to Formulas it searches the raw formula text in each cell, which means you can find every cell containing VLOOKUP and replace it with XLOOKUP in one sweep. Set to Values it searches the displayed result instead, which is what you usually want when cleaning data. There is also a Comments option for searching cell comments, although that is a niche use case in most modern workbooks.

Wildcards open up another world inside the dialog. The asterisk matches any sequence of characters, including an empty sequence, while the question mark matches exactly one character. A search for product* will match anything starting with the word product. A search for ?at will match three-letter words ending in at like cat, hat, and bat.

If you need to literally search for an asterisk or question mark, prefix it with a tilde to escape the wildcard meaning. So ~* finds a real asterisk character, and ~? finds a real question mark, and ~~ finds a literal tilde. Pair these patterns with Excel wildcards in the Find what field for surgical replacements.

Format-based search is another power feature. Click the Format button next to either field and you can specify font colour, fill colour, bold, italic, number format, or any combination thereof. This lets you find every red-coloured cell and replace its contents, or change every bold heading without touching the body text. Format search is unavailable in Excel for the Web, which is a common reason analysts switch back to the desktop client for serious data cleanup work.

The dialog supports two scope modes that matter for bigger workbooks. Selection-only replacement happens when you highlight a range first and then open the dialog with cells still selected; Excel limits the search to that range. Sheet scope covers the active tab, and Workbook scope covers every sheet. Be careful with Workbook scope on shared files, because a single careless Replace All can rewrite hundreds of cells across tabs you forgot existed.

For users coming from text editors like VS Code or Notepad plus plus, the absence of regular expressions in Find and Replace can feel like a step backward. Excel does not support regex in the dialog at all, which is the single biggest limitation of this otherwise excellent feature. The standard workaround is to switch to Power Query for any regex-flavoured replacement, which we will cover in detail later. Until then, wildcards are the closest thing the dialog offers, and they cover perhaps eighty percent of practical use cases.

The SUBSTITUTE function takes the manual Find and Replace operation and turns it into a formula you can embed anywhere. Its syntax is =SUBSTITUTE(text, old_text, new_text, [instance_num]). The first three arguments are required: the source string, the substring to search for, and the replacement substring. The fourth argument is optional and controls which occurrence to replace; leave it blank to swap every occurrence, or pass a number to replace only the first, second, third, and so on. SUBSTITUTE is always case-sensitive, which is something to remember when working with mixed-case data.

A simple example: =SUBSTITUTE(A1, "old", "new") replaces every instance of the word old with new inside the value of cell A1. If A1 contains the phrase the old cat sat on the old mat, the formula returns the new cat sat on the new mat. To replace only the first occurrence, write =SUBSTITUTE(A1, "old", "new", 1) and the formula will leave the second instance untouched, returning the new cat sat on the old mat instead. This precision is what separates SUBSTITUTE from the dialog box, which always replaces every match within a step.

One of the most useful applications of SUBSTITUTE is cleaning currency or percent symbols from imported numeric data. If column A contains values like $1,234.56 and you need to convert them to plain numbers, =VALUE(SUBSTITUTE(SUBSTITUTE(A1, "$", ""), ",", "")) strips the dollar sign and the thousands separator, then converts the resulting clean string to a number Excel can sum or chart. The nested SUBSTITUTE pattern is essential here because each call handles exactly one character replacement.

You can chain SUBSTITUTE calls as deeply as you need. To replace four different unwanted characters in one go, wrap four calls inside one another like =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, "$", ""), ",", ""), " ", ""), "€", ""). Excel does not impose a hard limit on nesting depth, although readability suffers fast and most analysts switch to a helper column or Power Query once they get past three or four levels. If your transformation needs more than five SUBSTITUTE calls, that is your signal to graduate to a more powerful approach.

SUBSTITUTE is also the standard formula for replacing line breaks inside a cell. Multi-line cells contain the invisible character code 10, which you can reference in a formula using CHAR(10). The formula =SUBSTITUTE(A1, CHAR(10), ", ") converts every line break in A1 into a comma followed by a space, collapsing a multi-line cell into a single readable list. This is invaluable when preparing data for charts, pivot tables, or CSV exports that cannot handle embedded newlines.

Another classic SUBSTITUTE trick is counting occurrences of a substring. The formula =LEN(A1) - LEN(SUBSTITUTE(A1, "x", "")) returns the number of times the letter x appears in cell A1. Subtracting the length of the cleaned string from the length of the original gives you the character count of the removed substring; divide by the length of the search term if you are counting multi-character patterns. This pattern shows up in dozens of practical scenarios, from counting commas in a CSV line to checking how many times a keyword appears in a paragraph.

Because SUBSTITUTE is a function, it preserves the original data and returns the modified version into a new cell. This non-destructive nature is exactly what makes it so useful inside larger formulas. You can also pair it with related text extraction techniques to slice, replace, and recombine strings in a single calculation.

The REPLACE function looks almost identical to SUBSTITUTE at first glance, but it works on completely different principles. Its syntax is =REPLACE(old_text, start_num, num_chars, new_text). Instead of searching for a pattern, REPLACE operates on character positions: it takes the source string, counts to the start position, removes a specified number of characters, and stitches in the replacement. This makes it perfect when the location of the text is fixed but the content might vary.

A typical example involves masking part of a credit card number. If A1 holds the string 4111222233334444 and you want to display it as 4111XXXXXXXX4444, the formula =REPLACE(A1, 5, 8, "XXXXXXXX") starts at character five, replaces the next eight characters with eight X marks, and leaves the first four and last four digits untouched. SUBSTITUTE would be powerless here because the digits in positions five through twelve change for every card and there is no consistent pattern to search for.

REPLACE shines in any context where you need to overwrite a specific range of characters regardless of what those characters happen to be. Date format conversion is a common use case. If you have dates stored as text in the form 20260315 and you want to inject hyphens for readability, =REPLACE(REPLACE(A1, 5, 0, "-"), 8, 0, "-") converts the string to 2026-03-15. Notice how the num_chars argument is set to zero, which means REPLACE inserts the new text without removing anything. This insert-only mode is something SUBSTITUTE cannot do at all.

Another useful pairing is REPLACE with FIND or SEARCH to make the position dynamic. If you do not know in advance where a substring starts but want to overwrite it once found, =REPLACE(A1, FIND("@", A1), 1, " at ") locates the at sign in an email address and replaces it with the word at surrounded by spaces. The FIND function returns the position, and REPLACE uses that position to know where to start cutting. This combination effectively turns REPLACE into a positional version of SUBSTITUTE, which is useful in narrow edge cases.

Be aware that REPLACE counts characters in the source language. With multi-byte characters such as Japanese or Chinese, Excel offers REPLACEB which counts by bytes instead. For most English-language workbooks REPLACE is fine, but if you find yourself working with mixed scripts, switch to REPLACEB to avoid off-by-one errors that can corrupt your output strings in unpredictable ways.

The most common mistake with REPLACE is miscounting the start position. Remember that Excel starts counting at one, not zero like most programming languages. The first character of a string is position one, not position zero. If you write =REPLACE(A1, 0, 5, "new") Excel will return a #VALUE error because zero is not a valid position. A useful sanity check is to use LEN(A1) to confirm the total length of the source string before committing to a start position and number of characters that lie within bounds.

Power Query Replace Values is the most powerful and most underused of the four methods, and it is the right answer whenever the same replacement must happen on every refresh of your data. To start, select any cell inside your table, open the Data ribbon, and click From Table or Range. Excel will load the data into the Power Query editor as a new query step.

Right-click the column you want to clean, choose Replace Values, and the familiar Find and Replace style dialog appears with two fields: Value To Find and Replace With. Type the values, click OK, and Power Query records the transformation as a step in the Applied Steps panel on the right.

The magic happens when you click Close and Load. Power Query writes the cleaned result back to Excel as a new table, and from that moment forward, every time you click Refresh, the same replacement runs automatically against the latest source data. This is a game changer for monthly reports, daily sales feeds, or any recurring workflow where the source file changes but the cleanup logic stays the same. You configure the replacement once and never touch it again.

Underneath the visual editor, Power Query writes everything in a functional language called M. The replacement step looks like = Table.ReplaceValue(Source, "old", "new", Replacer.ReplaceText, {"ColumnName"}). You can edit this M code directly by clicking the gear icon next to the step in the Applied Steps panel, which is how you unlock the full power of the underlying engine. The Replacer.ReplaceText function is the standard text replacer, but you can swap it for Replacer.ReplaceValue for whole-value replacement or write a custom function for regex-style matching.

For regular expressions specifically, M includes the Text.ReplaceText function and the more flexible Text.Replace, plus a third helper called Text.ReplaceRange for position-based work similar to REPLACE in Excel formulas. None of these support regex natively, but you can call them inside a List.Accumulate loop or pair them with Text.Split and Text.Combine to achieve regex-like behaviour. For genuine regex, you can also write a Power Query custom function that calls out to a small piece of JavaScript via Web.Contents and a local API, though most analysts find this overkill compared to just using SUBSTITUTE chains.

Replace Values in Power Query also handles null values gracefully, something the Find and Replace dialog struggles with. To replace blanks with a specific value, leave Value To Find empty and type your replacement in Replace With; Power Query treats empty as null and applies the transformation across every empty cell in the chosen column. This is the cleanest way to fill missing values in imported data before downstream calculations consume them.

The query refresh model is what makes Power Query genuinely production grade. You can save your workbook, send it to a colleague, and when they refresh against the same source file or folder, every cleanup step including all replacements rerun automatically. There is no risk of someone forgetting to apply a manual Find and Replace, and there is no audit gap about which cells were changed when. Power Query keeps a permanent record of every transformation in the Applied Steps panel, which doubles as documentation for anyone who inherits the workbook later.

If you have not yet explored Power Query because it looks intimidating, Replace Values is the perfect entry point. The dialog is identical to the one you already know from Find and Replace, and the only new concept is the Applied Steps panel that records what you did. Spend ten minutes building a simple cleanup query and you will see immediately why it has become the dominant data preparation tool inside Excel.

Choosing between the four methods comes down to three questions you should ask yourself before touching the data. First, will this replacement need to run again on future data? If yes, build it in Power Query so it refreshes automatically. If no, the dialog or a formula is fine. Second, do you need to keep the original data intact for audit or comparison purposes?

If yes, use a formula and put the result in a new column. If you can overwrite in place, the dialog is faster. Third, does the location of the replacement matter more than its content? If yes, reach for REPLACE. Otherwise SUBSTITUTE or the dialog will be more natural.

Performance is rarely an issue with text replacement in Excel, even on workbooks with hundreds of thousands of rows. The dialog and the SUBSTITUTE function both execute in well under a second on a modern laptop for typical column sizes. Power Query is slower because it loads data into a separate engine, but the refresh time still measures in seconds rather than minutes for any reasonable dataset. The exception is volatile functions or unnecessary array formulas wrapped around your replacement, which can slow recalculation noticeably on very large sheets.

A frequent source of confusion is the difference between Excel for Windows and Excel for Mac. The Find and Replace dialog uses Cmd plus H on Mac instead of Ctrl plus H, and the Options panel layout is slightly rearranged but contains the same controls. SUBSTITUTE and REPLACE behave identically. Power Query on Mac has been improving steadily and now supports almost every transformation that the Windows version offers, including Replace Values with full M code editing.

Excel for the Web is the most limited of the three platforms. Find and Replace works for basic value searches, but format-based search and Workbook scope are missing. SUBSTITUTE and REPLACE work in the browser exactly as they do on the desktop. Power Query is not available in the browser at all, so any production-grade automated cleanup needs to happen in desktop Excel and then be loaded into the workbook the web users open. Knowing these platform differences saves you the frustration of trying to perform a desktop-only operation in a shared browser session.

If you want to drill deeper into the underlying text functions that complement replacement, our guides on Excel text functions and Excel IF cell contains text cover the surrounding ecosystem of LEFT, RIGHT, MID, FIND, SEARCH, and conditional checks that often appear in the same formulas as SUBSTITUTE.

VBA Range.Replace deserves a brief mention even though most modern workflows favour Power Query for automation. Inside a macro, Range("A:A").Replace What:="old", Replacement:="new", LookAt:=xlPart, MatchCase:=False mimics the Find and Replace dialog programmatically. The LookAt parameter accepts xlPart to match substrings or xlWhole to match entire cell contents, and the MatchCase parameter toggles case sensitivity. This is the only method that lets you script replacement across hundreds of separate workbook files without manually opening each one.

For genuine regex-flavoured replacement, VBA can call the VBScript RegExp object via Set re = CreateObject("VBScript.RegExp"). Set the Pattern, Global, and IgnoreCase properties, then call re.Replace(sourceString, replacement) to apply the regular expression. This brings full PCRE-style pattern matching into Excel at the cost of writing a small amount of VBA, and it remains the closest thing to native regex inside Excel itself.

One advanced trick that combines SUBSTITUTE with REPT lets you split a delimited string into a fixed position. The formula =TRIM(MID(SUBSTITUTE(A1, ",", REPT(" ", 100)), (n-1)*100+1, 100)) extracts the nth comma-separated item from a string by padding each delimiter with one hundred spaces and then slicing at known positions. This technique predates the TEXTSPLIT function added in Microsoft 365 and is still useful in older Excel versions where TEXTSPLIT is unavailable. Even in modern Excel, this pattern shows up in shared workbooks that need backward compatibility.

Bulk replacement across multiple sheets uses the dialog with the Within dropdown set to Workbook. Open Find and Replace, click Options, and change Within from Sheet to Workbook. The next Replace All will sweep every tab in the file. Always save a backup first, because there is no undo across multiple sheets if the operation goes wrong. For repeatable bulk replacements across many files, VBA or Power Query are safer alternatives because they leave an audit trail you can inspect afterwards.

Format preservation is a subtle gotcha. The dialog and SUBSTITUTE both preserve the cell formatting of the result, but they cannot preserve rich formatting inside the string itself. If a cell contains a mix of bold and regular text and you run a replacement, the entire cell loses its mixed formatting and inherits the cell-level formatting only. The only way to preserve in-cell formatting through a replacement is to write VBA that walks the Characters collection of each cell and applies the formatting back after the swap, which is more trouble than it is worth in most cases.

Finally, there is a class of replacements that Find and Replace and SUBSTITUTE both refuse to handle: zero-width characters and certain Unicode control codes. These invisible characters often arrive with imports from web pages or PDFs and can prevent text matching even when the visible content looks identical.

The CLEAN function strips non-printable ASCII characters and is the standard first step for any dirty import. For Unicode control codes, a SUBSTITUTE with CHAR or UNICHAR of the specific code point is the surest way to remove them. Combining remove extra spaces techniques with CLEAN and SUBSTITUTE gives you a reliable three-step purification process for any text column.

Despite how simple text replacement seems, there are a handful of recurring mistakes worth flagging before they bite you. The first is forgetting that SUBSTITUTE is case-sensitive while the dialog defaults to case-insensitive. A SUBSTITUTE that searches for the word Apple will not touch any instance of apple in lowercase, even though the dialog with default settings would catch both. If you need case-insensitive SUBSTITUTE, the workaround is to wrap the source in LOWER or UPPER before substituting, then handle the case restoration separately if it matters for the output.

The second pitfall is replacing inside a formula by accident. With Look in set to Formulas, the dialog will rewrite formula text including function names, cell references, and arguments. This is a feature when you want to bulk-rename references after restructuring a workbook, but it is a hazard when you only meant to clean displayed values. Always confirm the Look in setting before clicking Replace All, especially on tabs that contain heavy formula logic.

The third common mistake is over-replacing because a shorter search string appears inside a longer one. Searching for cat and replacing with dog will also rewrite caterpillar to dogerpillar, which is rarely what you want. The fix is to enable Match entire cell contents when the replacement targets standalone values, or to include enough surrounding context in the search string to make matches unambiguous. Adding a leading or trailing space to both the find and replace fields often does the trick when working with word-level replacements inside larger sentences.

The fourth issue is the absence of regex in the dialog. This forces analysts to either approximate with wildcards or switch to Power Query for any pattern that wildcards cannot express. Recurring searches for things like phone numbers, email addresses, or specific date formats all benefit from real regex support, and Power Query is the cleanest path to that capability inside Excel.

The fifth and final gotcha is the DIV/0 or #VALUE error that can appear when REPLACE is called with an out-of-bounds start position or num_chars argument. Always wrap REPLACE in IFERROR if there is any chance the source string might be shorter than your formula expects. =IFERROR(REPLACE(A1, 5, 4, "new"), A1) returns the original value when the position is invalid, which prevents your output column from filling with red error markers. The same pattern works for SUBSTITUTE if there is a risk of feeding it a non-text value.

Excel for the Web supports basic Find and Replace with Ctrl plus H exactly like the desktop version, but the Options panel is missing several controls. Format-based search, Workbook scope, and the Look in dropdown all live only in desktop Excel. SUBSTITUTE and REPLACE work in the browser exactly as expected because they are pure functions that the calculation engine handles identically on every platform. If you need advanced replacement and your workbook lives in the cloud, open it in the desktop client temporarily to use the missing controls, then save it back.

To consolidate everything we have covered, here is the practical workflow that experienced Excel users follow when they need to replace text. Start by deciding whether you need an interactive one-off swap or an automated repeatable cleanup. For a one-off swap, press Ctrl plus H and use the dialog. For an automated cleanup, load the data into Power Query and add a Replace Values step. This single decision determines almost everything else about how you approach the task.

If you choose the dialog path, expand the Options panel before clicking anything and confirm the scope, case sensitivity, and Look in setting. A two-second sanity check prevents the most common errors. Type your search and replacement strings, then click Replace once to step through the first match. Confirm visually that the change looks right, then click Replace All to sweep the rest. Watch the count in the status bar and compare it to your expectation. If the count is wildly different from what you expected, undo immediately and revisit your search pattern.

If you choose the formula path, start by writing the SUBSTITUTE or REPLACE in a single new column next to your source data. Verify the output against the original visually for the first few rows, then drag the formula down. If the result looks correct, copy the column and paste as values to lock in the cleaned data, then delete the original column or leave it for audit purposes. Never apply formula-based cleanups in place without keeping a backup of the raw values, because once you paste-as-values you lose the ability to retrace your steps.

For Power Query workflows, name your Replace Values step descriptively in the Applied Steps panel. A label like Strip Currency Symbols is far more useful three months later than the default Replaced Value. Add a comment to the step using the right-click menu if the logic is non-obvious. Future maintainers, including future you, will thank you for the documentation. Pair this with our Power Query walkthrough for more reusable patterns.

Document your replacement choices in the workbook itself, either through a hidden tab or a notes section near the cleaned data. Anyone who inherits the file should be able to tell at a glance what was changed, why, and which method was used. This habit is the single biggest differentiator between an analyst who produces brittle one-off spreadsheets and one who builds maintainable analytical assets that outlast their original author.

Master these four methods and the next time you face a column of messy imported text, the right tool will already be in your hands. The dialog for quick edits, SUBSTITUTE for formula chains, REPLACE for positional overwrites, and Power Query for refreshable automation. Together they cover every realistic text replacement need you will encounter in office work, and the time you invest in learning them all is repaid many times over in the years that follow.