Blog

Manipulate Strings With Excel – Add Characters

By

May 9, 2013

2 comments

Excel

excel-manipulate-strings

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.

Tags

 

2 Comments

  1. I think the admin of this site is really working
    hard in favor of his web site, since here every material
    is quality based stuff.

    • Hello Austin! Thank you for the kind words. I really appreciate you taking the time to leave a message and am glad you’re enjoying the content.

Leave a Reply

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

*