Remove the apostrophe (') in Excel cell text values

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

[Roadie]

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'.

[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.

---
Regards,
Norman



"Connull" <Connull@discussions.microsoft.com> wrote in message
news:32E8EED5-E5DA-447B-9AD1-C95C1B6BFB02@microsoft.com...
> 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]

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" <Connull@discussions.microsoft.com> wrote in message
> news:32E8EED5-E5DA-447B-9AD1-C95C1B6BFB02@microsoft.com...
> > 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,

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


---
Regards,
Norman



"Connull" <Connull@discussions.microsoft.com> wrote in message
news:CA71569A-3D8F-4F6A-9268-DBF84C86FB98@microsoft.com...
> 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" <Connull@discussions.microsoft.com> wrote in message
>> news:32E8EED5-E5DA-447B-9AD1-C95C1B6BFB02@microsoft.com...
>> > 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,

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" <Connull@discussions.microsoft.com> wrote in message
news:CA71569A-3D8F-4F6A-9268-DBF84C86FB98@microsoft.com...
> 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" <Connull@discussions.microsoft.com> wrote in message
>> news:32E8EED5-E5DA-447B-9AD1-C95C1B6BFB02@microsoft.com...
>> > 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 (')
>>
>>
>>

[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

On Mon, 10 Jan 2005 21:53:01 -0800, "Connull"
<Connull@discussions.microsoft.com> 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" <Connull@discussions.microsoft.com> wrote in message
>> news:32E8EED5-E5DA-447B-9AD1-C95C1B6BFB02@microsoft.com...
>> > 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 (')
>>
>>
>>

[Varmentr]

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.

[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.

[pbz]

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

Thanks, Gord! This worked well for me and was super easy to do! You don't know the hours I'd spent trying to get rid of this annoyance!
You're a peach!

[mattmetzger]

...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!

[davimac]

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

[NST806]

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!

...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.

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

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

+1

[Vale_Nimi]

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.

[cruisy]

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

[tinavh]

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~