Excel Snippets: Substitute Function

I 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:

=SUBSTITUTE(A2,"Apples","Oranges")

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