Excel Substitute function

2 small things that make fairly regular data munging tasks a little easier:

Excel

When dealing with lots of adhoc data and relational databases, you want to be able to generate insert/update SQL statements in excel, based on some data in the spreadsheet.

Typically you will have some template SQL statement with placeholders for data that varies from row to row. Here is one approach to accomplishing this in Excel with relative ease.

Put your SQL template in cell A1. Lets say my template looks like this:

insert into some_table (one, two) values (@some_value, '@some_string_value')

The funny looking  @ denote placeholder where we want to insert our variable content.
This turns out to be fairly easy to do in Excel, if you use SUBSTITUTE() function.

=SUBSTITUTE(SUBSTITUTE($A$1, "@some_value", B1), "@some_string_value", B2)