+ Reply to Thread
Results 1 to 3 of 3

Leading single quote

  1. #1
    AA2e72E
    Guest

    Leading single quote

    Excel puts a leading single quote in text cells when populated from SQL and
    without recourse to Excel itself. I know that the single quote does not print.

    1. Is there a way of preventing the single quote prefix from being used?
    2. Is there a way of using Replace to remove the single quote?

    The only way I have found of removing the single quote is by copying the
    range & using Paste Special (Values): the range must be copied to a different
    location. The single quote does not disappear when copied in the same place.

  2. #2
    Toppers
    Guest

    RE: Leading single quote

    Hi,
    This worked for me:

    Sub RemoveQuote()
    Dim rng As Range, cell As Object
    Set rng = Range("a1:a10")
    For Each cell In rng
    cell = Format(cell, "@")
    Next cell

    End Sub

    HTH

    "AA2e72E" wrote:

    > Excel puts a leading single quote in text cells when populated from SQL and
    > without recourse to Excel itself. I know that the single quote does not print.
    >
    > 1. Is there a way of preventing the single quote prefix from being used?
    > 2. Is there a way of using Replace to remove the single quote?
    >
    > The only way I have found of removing the single quote is by copying the
    > range & using Paste Special (Values): the range must be copied to a different
    > location. The single quote does not disappear when copied in the same place.


  3. #3
    AA2e72E
    Guest

    RE: Leading single quote

    Thanks. It does.

    However, applying that format directly using the menu does not. Strange!

    "Toppers" wrote:

    > Hi,
    > This worked for me:
    >
    > Sub RemoveQuote()
    > Dim rng As Range, cell As Object
    > Set rng = Range("a1:a10")
    > For Each cell In rng
    > cell = Format(cell, "@")
    > Next cell
    >
    > End Sub
    >
    > HTH
    >
    > "AA2e72E" wrote:
    >
    > > Excel puts a leading single quote in text cells when populated from SQL and
    > > without recourse to Excel itself. I know that the single quote does not print.
    > >
    > > 1. Is there a way of preventing the single quote prefix from being used?
    > > 2. Is there a way of using Replace to remove the single quote?
    > >
    > > The only way I have found of removing the single quote is by copying the
    > > range & using Paste Special (Values): the range must be copied to a different
    > > location. The single quote does not disappear when copied in the same place.


+ 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