Need help with Excel. Who is a real Excel genius?!?

Question

Alright. I will just give you an example of what im trying to do, will be easier than explaining.
I want to take a list of keywords, e.g. dog, cat, pig, bird.
I want to use the replace function and replace each one of these words with the xxx in this list.
xxx pets
xxx food
xxx shelter
xxx toys
so in the end it would look like
dog pets
dog food
dog shelter
dog toys
the same for each word.
Now I know how to do it one at a time, word by word, but I have a list of over 5000 words I want to replace in a list similar to that. It will take me forever to do all 5000 if I have to do each replace one-at-a-time.
Please help and tell me there is a way...
and the quicker the answer the better ive gotta go live with this asap (im in web advertising and design)

thanks guys...and gals...

its just im looking for a macro or something like the replace function but to do all that at once...doing anything one at a time like that for 5000 words is too much.
I want to tell the program that all the words on this sheet need to be individually changed out for the xxx's in each set....so all 5000 words make their own set. It doesnt seem that tough to get done i just dont know how.

i dont know if you all understand....i have 5000 different words and I want to get it all done at once. I cant replace all with just dig...thats just one set. I would then have to do it 4999 more time for every other word. What macro exists to tell the program to do THAT for every word in the list???

Answer 1

First put your 5000 words in the column A (A1 to A5000)

Then Replace the "xxx" string by using find and replace command (keep the replace box blank)

Now put the 4 words that you want to combine from B1 to E1.

In the column F1, give the following formula
=CONCATENATE($A1, , B1)
(put the dollar mark without fail and also notice i have left some space in the middle.i.e in the second box of the formula, just press space bar and move on to the 3rd one)

then copy that formula to G1,H1 and I1.

Now copy all the columns from F1 to I1 and paste it somewhere or in that place itself as values(paste special)


Now all you have to do is combine those 4 columns and sort.
Copy all those 4 columns into one column and sort.

Goodluck

Answer 2

Don't use find and replace.

First create a list of just the first set of words. Highlight them and then drag the small square in the bottom right corner down as far as necessary to make the correct number of sets.

Now sort the list so you have each set together.

In the next column put the second list.

Highlight it and drag as before.

You will now have what you need.

For example.
dog
cat
mouse

Duplicate
dog
cat
mouse
dog
cat
mouse
dog
cat
mouse

Sort
dog
dog
dog
cat
cat
cat
mouse
mouse
mouse

Add second column.
dog pet
dog food
dog shelter
cat
cat
cat
mouse
mouse
mouse

Drag second column
dog pet
dog food
dog shelter
cat pet
cat food
cat shelter
mouse pet
mouse food
mouse shelter

Answer 3

First, with such a large file you don't want to have to retype, I would Save AS and save a copy of the file. Then, I would work on the copy. When you get it to your satisfaction, copy it back to the original.

Select the column
click Edit
cl Replace
replace - xxx pets
with - dog pets
cl replace all

***keep your eye on the undo button - just in case.

Another option I use at times when word-processing capabilities are required (like changing case), I copy the entire file to Word. Adjust it, and copy back to Excel.

Answer 4

Go to the formula bar at the top and type in an '=SUM' followed by dragging a box over the first range of cells you want to add up (in your case they are all in row 2 so just select them all) then put in a '/' and the number you want to divide by. To add the % symbol just look for the icon in the formatting options at the top of the screen.

Answer 5

Put your first list in columns (B1:Z1)
Put your second list in rows (A2:A5001)
(Use copy and paste special to transpose a list if you need to.)
In cell B2 type:
= REPLACE ( B$1 , FIND ( "xxx" , B$1 ) , LEN ("xxx") , $A2 )
Copy this cell to range (B2:Z5001)
There is a big range with your list.

Answer 6

go to 'edit' - 'replace' after entering your criteria with find=xxx and replace=dog, hit the 'replace all' button, should be to the left of the 'replace' button.

Answer 7

Bill Gates is a real Excel genius!