Adding or appending text to contents of cells. November 11, 2008
Posted by engjake in Uncategorized.Tags: add text, append, Excel
add a comment
Ok, another help article more for myself, but maybe somone else will benefit from this one.
I have a row of data in an excel worksheet. I want to add a file name extention to the contents like .jpg or .gif. Every name is unique so I can’t do a find and replace so how do I add an extension to all of these file names. I figured out a fairly simple solution thanks to a google search so I am recording it here so I can find it again the next time I need to do this.
If you select the entire column, then right click and go to “Format Cells” Select custom at the bottom of the list and then in the text field above the list of custom formating options you can type in @”.jpg” where .jpg is the extention you want to add.
One catch: you can’t insert some pieces of code like “> closing tag instead of the file name. It just doesn’t work for some reason. For this I thought I might add a unique extension like 10005 to the end of every cell and then do a find and replace, but Excel’s find and replace won’t do that for some reason. I’m still looking for the solution to this. I’ve done it before I just can’t remember what I did.
Using Find and Replace in Microsoft Excel October 29, 2008
Posted by engjake in Uncategorized.Tags: Excel, Find and Replace, Microsoft Excel
add a comment
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.
