How do you get rid of leading apostophes within Excel?

K

Kelly

Upon exporting data into Excel, I find that some of the columns have cells
that include leading apostrophes that I cannot get rid of. Is there any way
to do so?
 
J

John Nurick

Hi Kelly,

The apostrophes force Excel to treat the values as text even if they
look like numbers. Normally they don't do any harm and can be left
there. If you do need to get rid of them, you can use something like
this little Excel VBA procedure:

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub

See also: Convert Text to Numbers in Microsoft Excel 2002
http://support.microsoft.com/default.aspx?scid=kb;en-us;291047
 
K

Kelly

Thanks, John!

Now where do I go within Excel to incorporate that code you provided?

Thanks,

Kelly
 
J

John Nurick

Put in a code module, and make sure when you save the module that you
don't name it "RemoveApostrophes".

Then you can run it from the menu command, I think it's Tools|Macro.
 
Top