I have a .csv document in Microsoft excel that I import into Drupal that gets data pasted into it from Word. But when I try to import it, I usually get errors due to curly quotes, dashes, hyphens and all the other fancy styles that Word likes to put in its documents and that ends up getting copied over into the.csv file. So what I end up doing is manually find/replace these items one by one until I get no more errors.

I'd like to speed up this manual process so is there a formula I could run in excel that would convert the 'fancy' characters back into their standard ones so that my database could import them properly?

Pretty much I need the following to happen:

"‘" = "'"
"’" = "'"
"”" = '"'
"“" = '"'
"–" = "-"
"—" = "-"
  • You could do this with a simple PowerShell script (see stackoverflow.com/questions/60034/… ) or CMD script (superuser.com/questions/1004430/… ) – DrMoishe Pippik Sep 12 '19 at 14:59
  • I've tried those PowerShell commands, however, it ends up corrupting the output file and I'm unable to open them. I'm just hoping there's a Function that can be created to do all this in one shot. – ACanadianCoder Sep 12 '19 at 15:14
  • You need something specific when a single CSV value starts with (presumably it wouldn't start with ). In those special cases, XL sees the converted ' as an explicit text identifier and doesn't show it or export it. – user385793 Sep 12 '19 at 21:04

A way around manually finding and replacing the characters is recording a macro to do it for you.

  1. Go to file → options → Customize Ribbon
  2. Under main tabs place a marker at “Developer”. Now close the option window.
  3. Click on the developer ribbon and press “record macro”. Now find and replace the characters, then stop recording.

Now a macro has been recorded that you can play again for your next file.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.