Excel Snippets: Substitute Function

Microsoft ExcelI might not post many Excel snippets, but I’m collecting them into a small Excel Snippets series to make them easy to find.

I tend to do data manipulation in SQL Server rather than Excel, as it is generally easier to do it in SQL where I can run commands sequentially. However, there are times when I do need to do it in Excel.

I recently needed to replace one part of a string of text and really had trouble doing so. This was because I am used to using REPLACE in SQL and str_replace in PHP and expected the REPLACE function in Excel to allow similar replacement. However, I rapidly found that this was not the case.

I did some exploring and found that the command in Excel is actually SUBSTITUTE:


This will replace the word apples with oranges in the string in cell A2.

What should we write about next?

If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.

Your Name

Your Email

Suggested Topic

Suggestion Details

Leave a Reply

Your email address will not be published. Required fields are marked *