Every time I move data from Access into Excel each cell magically gains a
leading ' . I have tried trimming the cell, search & replace, text to
columns, and looking in Help. The ' is messing with some of the calculations
I need to run on the sheet. I really want to delete all of them.

Try a different Number format in Format-Cells, Number tab. Excel may be
reading the Access data as text.

"SLW" wrote in message
...
Every time I move data from Access into Excel each cell magically gains a
leading ' . I have tried trimming the cell, search & replace, text to
columns, and looking in Help. The ' is messing with some of the
calculations
I need to run on the sheet. I really want to delete all of them.

Sub nopreapostophe()
For Each c In Selection
If c.PrefixCharacter = "'" Then c.Value = c.Value
Next
End Sub

Run above AFTER highlighting your data's Cell Range.
HTH


"Richard Neville" wrote in message
k.net...
Try a different Number format in Format-Cells, Number tab. Excel may be
reading the Access data as text.

"SLW" wrote in message
...
Every time I move data from Access into Excel each cell magically gains a
leading ' . I have tried trimming the cell, search & replace, text to
columns, and looking in Help. The ' is messing with some of the
calculations
I need to run on the sheet. I really want to delete all of them.

Hi SLW,

Simple 1-line macro. To make available all the time, put it in a
Module in the VBE of your Personal.xls. You can then assign to toolbar
button, and/or shortcut key and/or menu. Then highlight the cells in
which you want to remove the " ' " and run the macro. (I gave the macro
the name RemoveApos - you can call it anything you want.)


Sub RemoveApos()
Selection.Value = Selection.Value
End Sub


Hope this helps.
Paul

Paul,
Very cool,
Blushingly,
Jim

"Paul D. Simon" wrote in message
oups.com...
Hi SLW,

Simple 1-line macro. To make available all the time, put it in a
Module in the VBE of your Personal.xls. You can then assign to toolbar
button, and/or shortcut key and/or menu. Then highlight the cells in
which you want to remove the " ' " and run the macro. (I gave the macro
the name RemoveApos - you can call it anything you want.)


Sub RemoveApos()
Selection.Value = Selection.Value
End Sub


Hope this helps.
Paul

Thanks very much Jim, but YOURS is much cooler and a MUCH better macro
than mine because yours first tests for the existence of an " ' "
prefix. Mine doesn't, and that would spell disaster if the range of
cells the user selects unintentionally happens to contain one of more
formula cells.

For example, let's say that the range A1:A3 contains the following:

' Bread
=B1+C1
' Butter

Your code would remove the apostrophe prefix from cells A1 and A3 and
would correctly leave the "=B1+C1" formula in A2 unchanged. My code
would also remove the apostrophe prefix from cells A1 and A3 but would
inadvertently change the formula in A2 to a value. Imagine the
problems this would cause if the user was unaware that he or she had
done this. (And EditUndo will NOT undo a macro.)

So, for anyone reading this, for safety sake go with Jim's version, not
mine.

Paul

Thank you gentlemen for your assistance. There is another set of answers,
one from another friend, and one I figured out from looking at the friend's
spreadsheet.

1) Highlight cells, - Edit - Clear - Format

2) Copy cells and Paste Special - Values into a new worksheet or workbook

I have the Paste Special button added to my desktop, so I like # 2 best, but
those of us who like menus will like #1 better.

I'll give you a tip in return. If you are right handed, use your mouse with
your left hand. Leave the buttons the same, just turn it a little on the
desk. Give it 3 days and you will never go back to a right handed mouse.
Reason: If you are right hand dominant and use Excel a great deal, there are
so many times you need to use the mouse and keyboard in combination. Why
keep moving your right hand back and forth? The first 1 1/2 days you'll want
to throw your mouse away forever, but keep at it. By the end of day 2 you'll
know it's going to be worth the effort & by the end of day 3 you will be
trying to convert your friends. I think my speed went up by over 60% when it
occurred to me to switch.

Have a great day!!!

Angels on your shoulders!

"Paul D. Simon" wrote:

Thanks very much Jim, but YOURS is much cooler and a MUCH better macro
than mine because yours first tests for the existence of an " ' "
prefix. Mine doesn't, and that would spell disaster if the range of
cells the user selects unintentionally happens to contain one of more
formula cells.

For example, let's say that the range A1:A3 contains the following:

' Bread
=B1+C1
' Butter

Your code would remove the apostrophe prefix from cells A1 and A3 and
would correctly leave the "=B1+C1" formula in A2 unchanged. My code
would also remove the apostrophe prefix from cells A1 and A3 but would
inadvertently change the formula in A2 to a value. Imagine the
problems this would cause if the user was unaware that he or she had
done this. (And EditUndo will NOT undo a macro.)

So, for anyone reading this, for safety sake go with Jim's version, not
mine.

Paul