Remove the apostrophe (') in Excel cell text values
 

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 (')

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.

---
Regards,
Norman



"Connull" wrote in message
...
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 (')

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" wrote:

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.

---
Regards,
Norman



"Connull" wrote in message
...
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 (')

Hi Connull,

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


---
Regards,
Norman



"Connull" wrote in message
...
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" wrote:

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.

---
Regards,
Norman



"Connull" wrote in message
...
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 (')

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


---
Regards,
Norman



"Connull" wrote in message
...
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" wrote:

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.

---
Regards,
Norman



"Connull" wrote in message
...
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 (')

Connull

One more to look at. Stretching here<g

If ToolsOptionsTransition. "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

On Mon, 10 Jan 2005 21:53:01 -0800, "Connull"
wrote:

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" wrote:

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.

---
Regards,
Norman



"Connull" wrote in message
...
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 (')