removing auto-format /smart quote characters from excel and access data

  • Thread starter Thread starter Caitlin
  • Start date Start date
C

Caitlin

someone passed me an excel doc with all the smart quotes
and microsoft auto-formatting turned on. i can't for the
life of me get the auto-formatting out of those documents.

- i have saved as text and opened in wordpad
- i have tried opening word, turning off the
autoformatting, pasting the text.
- i have copied/pasted and sent to myself in an text email
(the auto-formatting came out, but the text wrapping
didn't)


AAAAAAAAAAAGH!!!!!!!!!!

thanks!!!
 
Hi Caitlin, if its a DOC it is a WORD document, if the extension is XLS then
you are in the right NG. Please cofirm.

Gilles Desjardins
 
Sometimes you can just Edit|Replace the characters.

For example the smart leading quote has a character code of 147.

I could do
Edit|replace
replace: hit and hold the alt-key and type 0147 from the numeric keypad
with: " (single quote mark.

And then replace all.

But the close smart quote is character 148. This technique didn't work.

But I could run a macro that would clean up those characters. The bad news is I
don't know all of the things that word "helps" with. (But the emdash is
character is 150 and the straight quote is 34.)

Option Explicit
Sub testme01()

Dim FromChars As Variant
Dim ToChars As Variant
Dim iCtr As Long

FromChars = Array(Chr(147), Chr(148), Chr(150))
ToChars = Array(Chr(34), Chr(34), "--")

If UBound(FromChars) <> UBound(ToChars) Then
MsgBox "design error--make from/to match"
Exit Sub
End If

For iCtr = LBound(FromChars) To UBound(FromChars)
ActiveSheet.Cells.Replace What:=FromChars(iCtr), _
Replacement:=ToChars(iCtr), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Next iCtr

End Sub

So your job will be to find all the funny codes and find their replacements.

This code matches up the first "FromChars" with the first "toChars" and keeps
going. You can put the character or the chr() equivalent.

Chip Pearson has a nice program to help. It'll tell you the ascii value for
each character in a cell.

http://www.cpearson.com/excel/CellView.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
click on Tools|macro|macros...
click on the macro name (testme01--but you could rename it to something
meaningful!)
and then click run.
 
Back
Top