SUBSTITUTE word in multiple cells

**WEEZY760** · 03-22-2012, 02:31 PM

Good Afternoon Excel Forum,

I tried using the formula that was posted in this tread but I don't think it's the proper one to use:
http://www.excelforum.com/excel-2007...-the-rest.html

Here is what I'm trying to do:

Cells A1 - A4 have the following list:
alabama home
alabama home insurances
alabama home insurance
alabama home insurance warranty

Cells B1 - B4 have the following list:
alaska home
alaska home insurances
alaska home insurance
alaska home insurance warranty

Now I want cells C1 - C4 to say the same thing but substitute "alaska" for now, "arizona"

I can easily input the formula to make C1 convert to "arizona home". But if I try to highlight the entire list so that it automatically generates it, it either says the VALUE error or it just converts the first one and leaves C2 - C4 blank.

Is there a way to do multiple lists?

(I promise I'm not being lazy and not just copying and pasting over and over again because its only 4 rows. I am doing an excel file with hundreds of rows so trying to figure out how to save time instead of copying and pasting over and over again.)

Thank you for any help and time I can get!

-Joe

**dilipandey** · 03-22-2012, 02:42 PM

Hi Joe,

I believe you are trying to convert the entire list in one go using the formula but you need to enter formula for each row. Just enter it in one cell i.e., cell c1 and drag it down.

Also have a look at the function Replace as well alongwith Substitute,

Post the sample file if you face any challenges. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**WEEZY760** · 03-22-2012, 02:58 PM

HOLY COW

Thank you. I knew something was right under my nose and then a genius, you, came along to push me into it. THANK YOU, just saved me a ridiculous amount of time.

Joe

**WEEZY760** · 03-22-2012, 03:01 PM

Well actually.

Now I have a new question. If I want to copy and paste the columns of information into something like an online website forum, all I get are formulas. I'm trying to look up how to convert them into just normal text now but they are just formulas now.

These are basically key words to help get my advertising out more to a larger audience but if I copy and paste these words they are now just formulas and not actual words.

**WEEZY760** · 03-22-2012, 03:02 PM

NEVERMIND

Ignore my last post. It works perfectly. Thank you

**carsto** · 03-22-2012, 02:44 PM

Do your Column Headers contain the State Name?
If so you could use
=A$1 & " home"
=A$1 & " home insurances"
=A$1 & " home insurance"
=A$1 & " home insurance warranty"
& copy over

Otherwise you may have to Copy & Paste the entire column (data only) then use Edit > Replace to replace alabama with alaska