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.