+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Valori
    Guest

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

  2. #2
    fanpages
    Guest

    Re: 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 ]

  3. #3
    Tom Ogilvy
    Guest

    Re: 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" <Valori@discussions.microsoft.com> wrote in message
    news:C16002E1-C603-41E5-B0C7-6ED1801D24EB@microsoft.com...
    > 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????




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1