the ' symbol ends the SQL string, how do you replace it Excel
 

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????

the ' symbol ends the SQL string, how do you replace it Excel
 

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 ]

the ' symbol ends the SQL string, how do you replace it Excel
 

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????