excels at mediocrity

I recently download several hundred products from my shopping cart system and was looking for a quick way to update a whole bunch of url paths for images that were in html fields on the website. So what I had was a .csv file with a slew of cells filled with html. The find and replace feature on excel works wonderfully, but apparently it has a limit to the number of characters in a cell it will search. No matter how i changed formatting or “Find” perameters I kept getting this error that excel could not find the text. I have used the find an replace feature quite a bit before so I was baffled as to why it would not work. After an hour or so struggling with it I found the solution.

First i copied and pasted the column I needed to change and put it in a new workbook (just so i didn;t mess up the original). Then I saved the new workbook as an XML file. Opened the xml file in dreamweaver (i’m sure another html editor would work). Once in dreamweaver I was easily able to do the find and replace of the URL i needed. Then I saved the document (again as an xml file) opened it in Microsoft Excel and copied the column and put it back into the original document. This seemed to do the trick and all my URLS are now how working wonderfully. Too bad it took me 2 hrs to figure out a porcess that only took minutes. And so here it is in blog form so I can find the solution next time I need it.