I am creating a VBA button to pass info to an Access database. However the
original data contains the single quote in front of each entry. (To ensure a
text entry I'm assuming since this is another databases dump.)
But when I try to do a search and replace on this character from within
Excel, I'm told it isn't found. Even when I cut and paste it into the search
tool! The sheet is not protected.
I need a scriptable solution as this will how all the worksheets will be
provided to me and I do not want to have to translate each to with copy and
paste and new formulas etc.
Any help out there????

Hi Valori,

I am creating a VBA button to pass info to an Access database. However the
original data contains the single quote in front of each entry. (To ensure a
text entry I'm assuming since this is another databases dump.)
But when I try to do a search and replace on this character from within
Excel, I'm told it isn't found. Even when I cut and paste it into the search
tool! The sheet is not protected.
I need a scriptable solution as this will how all the worksheets will be
provided to me and I do not want to have to translate each to with copy and
paste and new formulas etc.
Any help out there????

Are you sure the apostrophe (single quote) character ' prefixes your values?

Excel treats any value prefixed with a single quote as a text entry.

The character will be shown in the Formula Bar, or in-cell, when
editing, but upon confirming the entry, the character is not shown;
hence you cannot search for it.

FYI: If you had an asterisk in a value, for example, as this is used to
signify a "wildcard" character in the search (and replace) dialog, you
would search for it by prefixing with a tilde ~ character.

That is, ~*


To overcome your issue, try exporting the Excel data to a text file &
see if the single quote character is actually part of the cell value(s)
or not.

BFN,

fp.
[ http://www.experts-exchange.com/M_258171.html ]

Ignore the single quote unless you find it is affecting your results. More
than likely it will have no affect.

--
Regards,
Tom Ogilvy


"Valori" wrote in message
...
I am creating a VBA button to pass info to an Access database. However
the
original data contains the single quote in front of each entry. (To ensure
a
text entry I'm assuming since this is another databases dump.)
But when I try to do a search and replace on this character from within
Excel, I'm told it isn't found. Even when I cut and paste it into the
search
tool! The sheet is not protected.
I need a scriptable solution as this will how all the worksheets will be
provided to me and I do not want to have to translate each to with copy
and
paste and new formulas etc.
Any help out there????