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.

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.

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.