I never Excelled in Excel

May 13, 2008 2:32 PM

I have no clue what I am doing in Excel, so this is going to be a bit hard to explain, but hopefully somebody can help me here. I want to give Excel some text, and have it output a string. Let me explain.

In A1 I type in something like

-^”some text”

In B1 I would like to see

-%5E%22some+text%22+

- stays - | ^ is changed to %5E | “ is changed to %22 | (space) is changed to +

I would continue in the same fashion down column A. The next thing I would like to do is take C1 (which is www.someaddress.com/q=) and add all the B column and then C2 (which is &something) and put the results in C3.

So C1+B1+B2+B3+B4…+C2
My output should be something like www.someaddress.com/q=-%5E%22some+text%22+-%5E%22some+other+text%22+&something

So, can anybody explain how to do this to an Excel dummy? 2007, if it matters.

posted by B(oYo)BIES to computers & internet (4 answers total)

It will probably be easiest to use the find and replace menu function to do your replacements. You can do it with a formula (using the FIND and REPLACE functions), but for with only three changes, you'll be better off using the menu function.

Once you've made the changes you need, then use CONCATENATE(C1,B1,B2,B3,...,C2).
posted by ssg at 2:50 PM on May 13, 2008

Bit difficult to explain, so I've knocked up an example of this... email me (as per my profile) and i'll send to you.

Basically using =substitute() to do the string replacements, and then concatenating all the strings together using "&" within the target cell.

E.g. =A1&B1 will give you the two cell values run together.
posted by saintsguy at 2:52 PM on May 13, 2008

I should provide a little more detail: to use the CONCATENATE function (or any function) put a "=" in front of it. If you want your output in cell C3, then put "=CONCATENATE(C1,B1,B2,B3,...,C2)" in C3. Also, if you want to keep your original text, just copy it into a new column, select the new column, and do the search and replace on that column only.

Also, please ignore the random extra word in my answer above.
posted by ssg at 2:58 PM on May 13, 2008

You want to create a user-defined function based on urlencode.
posted by odinsdream at 5:44 PM on May 13, 2008 [1 favorite]