Excel Character Replacement

October 5, 2005 8:25 AM

Excel Character Replacement

I have an inventory file in Excel that is uploaded to Amazon, and I need to replace a specific character with one that registers on their system correctly. If I copy a block of text from various applications I receive ” (smart quote) when I really want " (simple quote). It seems that Word and PowerPoint have a rule in Tools > AutoCorrect for disabling/converting these characters from one to the other, but there is no equivalent in Excel.

You'd think this would be a simple replacement operation, but every time I attempt to do it in the standard work sheet it flips out with "Formula is too long". If I copy the work sheet to a new document in a way that truncates the fields to 255 characters, it works just fine.

I need to be able to pull this replacement routine over the entire document, as the culled description fields are useless and I'm constantly updating the information in this file from various sources. What is the most simple workaround for this problem? I'm thinking I could problably implement a simple replacement script/macro, but I know nothing of the inner workings of Excel off the top of my head and I'm not finding very applicable results through my searches.

posted by prostyle to computers & internet (3 comments total)

You could export the Excel file to a comma-separated or tab-separated value text file, do the character replacement in Word and import the document back into Excel. Kind of laborious, but it's worked for me on numerous occasions.
posted by cog_nate at 8:51 AM on October 5, 2005

I'm not clear on why Find > Replace doesn't work for this. You can try this code, which does essentially the same thing (and consequently might throw the same error message):

Sub ReplaceQuotes()

Cells.Replace What:="”", Replacement:=""""

End Sub
posted by odinsdream at 9:14 AM on October 5, 2005

Thanks for the quick responses, both solutions are very helpful. I was leaning towards cog_nate's suggestion before I posted this question, but I really wanted to keep it all in Excel.

odinsdream's code works without a hitch! Thanks much!
posted by prostyle at 9:37 AM on October 5, 2005