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

Select the cells that you want to clear from apostrophes, choose in the menubar Edit --> Replace
In 'Find what', fill in your ' ,
In 'Replace', fill in nothing. Then click 'Replace all'.

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 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

On Mon, 10 Jan 2005 21:53:01 -0800, "Connull"
<[email protected]> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 (')
>>
>>
>>

Originally Posted by Connull

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

To remove the leading apostrophe from the text values, 1) copy all of the cells with the leading apostrophe's; 3) Go to either a new worksheet or workbook and copy the selected cells using: "Paste Special, Values." The copied cells should now appear without the apostrophe's and can be copied and pasted back into the original workbook.

Originally Posted by Paul

...this thread is over 7 years old so the original posters are likely no longer watching it. Please take that into account in future posts.

I'd like to add that google indexing makes content like this timeless. It doesn't really matter that the original posters are likely no longer watching, the rest of the world is. Thanks Varmentr!

Thanks Varmentr - your reply might be 7 years after the original question but it worked best for me!

Originally Posted by Varmentr

To remove the leading apostrophe from the text values, 1) copy all of the cells with the leading apostrophe's; 3) Go to either a new worksheet or workbook and copy the selected cells using: "Paste Special, Values." The copied cells should now appear without the apostrophe's and can be copied and pasted back into the original workbook.

This worked much better (and easier) than the solutions above. Thank you!

Originally Posted by Paul

...Thanks for posting a possible solution, however this thread is over 7 years old so the original posters are likely no longer watching it. Please take that into account in future posts.

Don't disparage new posters. Threads to problems aren't designed to be only for OPs. You're not running a help desk, this is a publicly visible forum. I came to this site from a Google search and only the 7 year old answer solved my problem.

Originally Posted by mattmetzger

I'd like to add that google indexing makes content like this timeless. It doesn't really matter that the original posters are likely no longer watching, the rest of the world is. Thanks Varmentr!

+1

Originally Posted by davimac

Thanks Varmentr - your reply might be 7 years after the original question but it worked best for me!

+1

Originally Posted by Paul

Hi Varmentr, welcome to the forum.

Thanks for posting a possible solution, however this thread is over 7 years old so the original posters are likely no longer watching it. Please take that into account in future posts.

3437 Days later - I found this thread useful.

Ditto re Varmentr (Thank you! ) Was just what I needed to solve a legacy text formatting issue from an old workbook that I have converted to Excel 2013.
Regards
Andy

Hi u'all ...

I've just joined this forum, having scoured the net for days 'n days for an answer. Before you shout at me, I shall go and look to see how to start a new thread but for the moment I have overcome my problem by saving as a csv and, after reopening, saving as a xlsx.

My problem is the apostrophes in blank/empty cells after having sent info from Pastel to Excel. Pastel uses the apostrophes as placeholders so that the Excel sheet resembles the printout. Being able to export to Excel is awesome but then, when you have several pages of rows and columns, "ctrl click, delete" becomes a long, brainless, time consuming, mind numbing bind. I'm clearly wanting to use the figures and these "blank" cells are not blank nor empty, resulting in errors when it becomes part of a formula. You'd think MS would have done something about this by now.

There are many brilliant suggestions, some from them (e.g. Ctrl H, find ~' ... ) but this does not work in the supposedly empty cells. Every solution I've found (+- 15) made my heart sing with joy but the songs have gone from heavy metal to a heart wrenching wrist gnawing break up ballad.

Anyhoo, the "saving as" works for the short term with both the text, numbers as well as the less than blank cells, specifically when you're dealing with huge sheets.

Will try to remember to add any earth shattering, mind blowing solutions as they pour in on the other thread I shall try and figure out how to start

Later then.
~T~