Remove the apostrophe (') in Excel cell text values

[Guest]

How can I remove the apostrophe (') from the text values in my spreadsheet. I
need to use this spreadsheet to import data into another program which then
gives errors due to the (')

 

[Norman Jones]

Hi Connull,

Copy a blank cell
Select the cells with the offending initial apostrophe
Edit | Paste Special | Check "Value" and "Add" | OK

Perhaps counter-intuitively, this works on text values.

 

[Guest]

Thanks Norman, this option removed the apostrophe from the numeric values but
I am still sitting with the problem of the apostrophe in front of the text
values. If you have any further suggestions I would be most grateful.

 

[Norman Jones]

Hi Connull,

The Copy (blank) | Paste Special | Add process clears leading apostrophes in
text values too - at least this works for me.

 

[Norman Jones]

Hi Connull,

If you want a VBA solution, try:

Sub DeleteApostrophes()
Dim rCell As Range

For Each rCell In ActiveSheet.UsedRange
If rCell.PrefixCharacter = "'" Then
rCell.Value = rCell.Value
End If
Next rCell

End Sub

 

[Gord Dibben]

Connull

One more to look at. Stretching here<g>

If Tools>Options>Transition. "Transition Navigation Keys" is checked you will
see an apostrophe in the cell if left-aligned text.

A ^ sign if center-aligned......a " sign if right-aligned.


Gord Dibben Excel MVP