PDA

View Full Version : Excel find and replace function



Susan B
25-08-2004, 11:39 AM
I have an Excel 2002 worksheet with over 4500 rows in which I need to do a search and replace.

What I want to do is search Column B cells for "WR" and replace the entry alongside it in Column A with "Yamaha".

Doing this manually over 4500 rows is going to take me forever but I am not sure how to write a script or formula that will repeat itself until it is done. In fact since I am not at all familiar with VB I can't even get a macro to do more than one search and replace, never mind do the lot. :p

Anyone have any suggestions, please?

rugila
25-08-2004, 11:47 AM
Goto the menu along top --> edit --> replace, and it more or less tells you what to do.

Has options etc. to replace contents of whole cell or part thereof.

Does it quickly ,easily and efficiently, no visual basic or anything needed.

Susan B
25-08-2004, 12:06 PM
Yes, I can replace the contents of the cell that it finds WR in but I don't want to change that one. I want to change the contents of the cell alongside it.

For example I have a number of rows where Honda (or Suzuki) is in column A and WR is in column B but all entries that have WR in column B should have Yamaha in the the cell alongside it in column A.

In the Find and Replace box I can't see anything where I can get it to change the contents of the adjacent cell, column A after finding WR in column B.

Does that make sense? I am horrible at explaining things.

andrew93
25-08-2004, 12:35 PM
Hi Susan

I'm not sure of any smart features or macros etc to do this but there is a workaround as follows :

If you insert a new column (lets say column C), and assuming the data starts at A3 enter the following formula :

=if(B3="WR","Yamaha",A3)

This formula will pull through the original value where it is not "WR" but will write the value ""Yamaha" in all instances of "WR". Copy this formula from cell C3 to C4503 - or wherever the data ends. Then select C3 to C4503 (or wherever the data ends), Edit, Copy, Click cell A3 (assuming the data starts at A3 as per before), Edit, Paste Special, Values, Ok. Delete Column C.

This will have the desired effect.

HTH

Andrew

Susan B
25-08-2004, 01:11 PM
Thanks Andrew, that did the job brilliantly. :-)

Some of the cells were empty and Excel put a 0 (zero) in those which I didn't really want but I soon got rid of them.

Now to fix up the other snafus. ;-)

Russell D
25-08-2004, 03:28 PM
Alternatively,
DataSort the entire data using Column B as the criteria, overwrite the first entry opposite "WR" in Column A with "YAMAHA" and drag it down opposite the last "WR" and re-sort the data.
If the order of entries is important, make an index column with ascending numbers as your last column before the initial Sort, then re-sort on this column at the end, then delete the index column.

Susan B
25-08-2004, 06:15 PM
Thanks Russell, that's a good suggestion as well. I'm bound to find that one quite handy at times. Don't know why I didn't think of it myself. :-)

Graham L
25-08-2004, 06:38 PM
Doh? ]:) :O :D

Russell D
26-08-2004, 08:17 PM
Alternatively, DataFilter Autofilter the data, select "WR" from the dropdown box on Column B and then overwrite the top entry in Column A with "Yamaha" and copy it down over the remaining entries.
Select DataFilter Autofilter again and all the data is revealed still in the original position.

Both Data Sort and AutoFilter are very useful tools.

Cheers