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.

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.

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.