My goal is to turn the string http://google.com into “http://google.com/*” for over 800 different domains, which is way too many to do manually. There are several ways to manipulate strings in excel using the CONCATENATE function, which is well described in this tech republic article, but I will share the approach I found to be most straightforward.

Use the ampersand (&) symbol to join text
I’ve got my list of 800+ domains in a column in excel. Let’s say I have the value http://google.com in A1. I can now use the ampersand symbol to add a double quote (“) to the beginning of the string. However, double-quotes are special symbols in excel, so instead of typing in the quote directly, I use the character symbol code CHAR(34). You can look up any character symbol in excel using the ascii character chart.

Formula: =CHAR(34)&A1
Output: “http://google.com

Next, I want to add a forward slash to the end of the domain.
Formula: =CHAR(34)&A1&CHAR(47)
Output: “http://google.com/

And now I want to add the asterisk…
Formula: =CHAR(34)&A1&CHAR(47)&CHAR(42)
Output: “http://google.com/*

And another double quote…
Formula: =CHAR(34)&A1&CHAR(47)&CHAR(42)&CHAR(34)
Output: “http://google.com/*“

And finally, a comma.
Formula: =CHAR(34)&A1&CHAR(47)&CHAR(42)&CHAR(34)&CHAR(44)
Output: “http://google.com/*“,

Now I can drag down the list of 800+ domains and excel will automatically add these extra characters to the beginning and end of the string.