SUBSTITUTE in Excel: occurrences and safe text replacement

SUBSTITUTE replaces a specified piece of text with another. In Polish Excel, its name is PODSTAW. It is easy to confuse it with REPLACE, which changes characters at a specified position. I choose between them after deciding exactly what should change in the source records and which information must remain intact.

The small synthetic examples below cover every occurrence, one selected occurrence and the difference between tidying a string and changing its meaning. I keep the source column so that each result can be compared with its input.

SUBSTITUTE and REPLACE need different information

Microsoft’s SUBSTITUTE documentation describes replacing a particular old string with a new one. REPLACE takes a starting position and a character count instead. Positional replacement is useful for a fixed layout, but requires a different assumption about the input.

=SUBSTITUTE(text,old_text,new_text,[instance_num])

The first argument supplies the text or source cell. The second is the fragment to find, and the third its replacement. The optional fourth argument selects an occurrence. Without it, all occurrences of the specified fragment are replaced. The occurrence number counts matches of the fragment rather than characters or words.

These examples use English names and commas between arguments. Polish formulas commonly use semicolons. If a copied formula is rejected before calculation, check the function name and regional separators. Quotation marks around literal text are part of the syntax too; they are not decoration added for readability.

Replace all occurrences or choose one

Enter the synthetic code AA-AA-AA in A2, then put this formula in B2:

=SUBSTITUTE(A2,"AA","BB")

The expected output is BB-BB-BB. In a separate cell, add the fourth argument:

=SUBSTITUTE(A2,"AA","BB",2)

This returns AA-BB-AA, because only the second occurrence of AA changes. The hyphens stay in place. The string is short enough to trace every match and see precisely what the occurrence number identifies.

The variant =SUBSTITUTE(A2,"-","",1) removes the first hyphen and produces AAAA-AA. The empty string "" is an intentional replacement. Before doing this to actual product identifiers, establish whether separators distinguish different keys. Similar-looking strings can carry different meanings in the system supplying the report.

A substring replacement does not recognize words

Enter cat and catfish in A3 and use:

=SUBSTITUTE(A3,"cat","dog")

The result is dog and dogfish. The operation follows a sequence of characters without understanding word boundaries. Here the replacement is mechanically consistent, yet changes more than a reader might intend. That makes it a useful reminder to inspect different record types before applying a rule to an entire column.

The same problem can affect automatic corrections to address abbreviations. A short fragment may occur inside a street name or a free-text comment. I first write the business condition: which mistake should change, and where can it occur? If the rule must recognize a whole field or category, replacing a substring everywhere may be too broad.

Treat letter case as part of the pattern. Do not design a cleanup rule on the assumption that one spelling automatically covers every variant. Standardizing case requires a separate decision, particularly for proper names and identifiers. Microsoft’s data-cleaning guide treats those transformations as distinct tasks rather than a single universal cleanup operation.

Choose content or position deliberately

For AB-123, both formulas below return CD-123:

=SUBSTITUTE("AB-123","AB","CD")
=REPLACE("AB-123",1,2,"CD")

The first searches for AB; the second changes two characters starting at position 1. If another record has a different prefix, REPLACE still changes its first two characters. SUBSTITUTE responds to the specified content. Decide whether the reliable feature of your input is a position or a known fragment before choosing the formula.

The more elaborate expression is not automatically the better one. In a controlled fixed format, positional replacement can be clear. When a known fragment appears in different positions, content-based replacement may be more suitable. Maintenance becomes difficult when the workbook preserves the formula but loses the explanation of the source structure it was written to handle.

Spaces and numeric text need separate rules

Removing every ordinary space with =SUBSTITUTE(A4," ","") is appropriate only when none of those spaces carries useful information. It turns AB 12 into AB12, but also turns New Town into NewTown. Do not use it as a universal method for cleaning names or descriptions simply because the result looks more compact.

TRIM serves a different purpose: it tidies ordinary spaces while preserving a single space between words. Its documentation also explains the limitation involving nonbreaking spaces. If strings look identical but behave differently, identify the actual characters first. The apparent width of a gap on screen does not establish which character occupies it.

Replacing a decimal comma with a period also requires knowing what the source represents. A comma can indicate a decimal separator in one input and a thousands separator in another. Establish the input convention before designing a conversion. For repeatable imports with explicit type handling, consider Power Query.

Keep the source and inspect exceptions

Keep the original in column A, the result in B and a short rule description beside them. Inspect a record that should change, one that is already correct and one containing a similar fragment in a different context. Also consider an empty input or an error in the source. Different conditions may need different responses rather than one broad replacement.

For multiple substitutions, show intermediate stages in separate columns. Order can matter if the first replacement creates text that the second operation searches for. Only simplify the expression after understanding the full sequence. A single long formula occupies less worksheet space, but can make an unexpected result harder for another person to explain.

Try the rule on a small exercise

For AA-AA-AA, replace only the third AA with CC. Separately, remove every hyphen from the original input. Expected results are AA-AA-CC and AAAAAA. Then explain why a rule that replaces cat is unsuitable when the requirement is to change only complete words and preserve longer words containing those letters.

Before using the expression in your own workbook, add one sentence stating which fragments may change. That decision is part of data quality. A formula can perform its specified replacement perfectly while removing information the report was supposed to preserve.