How can I remove hidden apostrophe in Excel?

Jim Moberg

unread,

Feb 14, 2007, 1:00:38 PM2/14/07

to

Hi,

I have a spreadsheet of data and a number of the columns have data that is
preceeded by a hidden apostrophe. The apostrophe can only be seen when you
click on the cell. I have looked and can't find a post that addresses this.
Can anyone out there tell me how to remove this. I have tried using the trim
function in conjuction with the clean function and it didn't work. Ack!

Nozza

unread,

Feb 14, 2007, 1:08:47 PM2/14/07

to

If the cells are all numbers, then add 0 to them in a new column.

This will convert the string 7 to a numeric 7.

eg If A1 is equal to '7 then in cell B1 use the formula =A1+0

HTH

Noz
--
Email (ROT13)
abmmn_...@lnubb.pb.hx

Gord Dibben

unread,

Feb 14, 2007, 1:13:59 PM2/14/07

to

Is the apostrophe visible only in the formula bar?

The CLEAN function works for me.

=CLEAN(A1) then copy>paste special>values>ok>esc.

Another thought, if you have Tools>Options>Transition>Transition Navigation Keys
options checkmarked, you could be seeing the Lotus alignment mark.

' for left aligned
^ for centered
" for right

Gord Dibben MS Excel MVP

On Wed, 14 Feb 2007 10:00:38 -0800, Jim Moberg
<JimM...@discussions.microsoft.com> wrote:

Bill Ridgeway

unread,

Feb 14, 2007, 1:12:25 PM2/14/07

to

The ' formats the cell to align left. You may also see carat ^ (not sure
about correct spelling) which centres text and " which aligns text to the
right. You can't delete it and there's nothing to worry about.

Regards.

Bill Ridgeway
Computer Solutions

"Jim Moberg" <JimM...@discussions.microsoft.com> wrote in message
news:04862269-5D4D-4C77...@microsoft.com...

Jim Moberg

unread,

Feb 14, 2007, 1:41:12 PM2/14/07

to

I did try that and it didn't work for me.

Jim Moberg

unread,

Feb 14, 2007, 1:43:19 PM2/14/07

to

It looks like I found the solution. I saved the file as a csv file type and
after I brought it into excel again I didn't see the apostrophe.

Dave Peterson

unread,

Feb 14, 2007, 1:54:31 PM2/14/07

to

Is a macro ok?

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constants in selection!"
Exit Sub
End If

For Each myCell In myRng.Cells
If myCell.PrefixCharacter <> "'" Then
myCell.Value = "'" & myCell.Text
End If
Next myCell

End Sub

Select a range and try it out.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

--

Dave Peterson

Lori

unread,

Feb 14, 2007, 4:19:34 PM2/14/07

to

You could also try selecting a column and then Data > Text to columns
> Finish.

On Feb 14, 6:00 pm, Jim Moberg <JimMob...@discussions.microsoft.com>
wrote:

karenellis

unread,

May 28, 2008, 10:36:24 PM5/28/08

to

This formula worked like a charm for me:
=VALUE(cell containing apostrophe)
Then you can copy, paste special, and click Values to replace the cells containing apostrophes.

DILipandey

unread,

May 29, 2008, 2:55:00 AM5/29/08

to

Hi, you can multiply those value by 1 which containes hidden apostrophe.
After doing this you can have those value moved to right side of the cell and
you can see that hidden apostrophe no more is there in the cell. thanks

--
Dilip Kumar Pandey
MBA, BCA, B.Com(Hons.)
dilip...@yahoo.com
dilip...@gmail.com
New Delhi, India

"unknown" wrote:

>

Rick Rothstein (MVP - VB)

unread,

May 29, 2008, 5:10:58 PM5/29/08

to

You can remove the apostrophes from numerical values directly. Select the
cells in column, click Data/Text To Columns on Excel's menu bar and then
click the Finish button. If you have more than one column with your "text
numbers", then you will have to do the above column-by-column one-at-a-time.

Rick

"Karen Ellis" wrote in message news:20085282236...@cox.net...

billcrighton

unread,

Oct 27, 2008, 11:10:17 PM10/27/08

to

There may be better methods but my fix is to copy a cell that does not have the apostrophe, then paste special the cells I want to remove the apostrophe from and select format. Thats it!

ShaneDevenshire

unread,

Oct 28, 2008, 3:00:01 AM10/28/08

to

Hi,

The solution depends on the type of data:
Suppose it is numbers or dates that have the apostrophe at the beginning:
1. Select an empty cell and choose copy
2. Select all the cells that have the dates or numbers
3. Choose Edit, Paste Special, Add.
If the entries were dates you will need to format them as dates.

Suppose the data is text and is located in A1:A100
1. In an empty cell enter the formula
=LEFT(A1,10^10) the 10^10 is overkill but it just makes sure
you get the largest possible text entry.
2. Copy the formula down as far as necessary
3. Select all the formulas and copy them
4. Choose Edit, Paste Special, Values

If this helps, please click the Yes button.
--
Thanks,
Shane Devenshire

"unknown" wrote:

>

RagDyeR

unread,

Oct 28, 2008, 10:53:28 AM10/28/08

to

Can't see the entire thread here, but ... for numeric values,
simply open and close TTC.

Select the column of values, then, from the Menu Bar,
<Data> <TextToColumns> <Finish>
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

<Bill Crighton> wrote in message news:2008102723...@xtra.co.nz...

Adrian Horth

unread,

Aug 4, 2010, 7:43:30 PM8/4/10

to

1) Take one cell and delete the single quote mark.
2) Using the format painter, select this cell.
3) Apply the format to all other cells.

Done.
Cheers,
Adrian

JimMober wrote:

How can I remove hidden apostrophe in Excel?
14-Feb-07

Hi,

Previous Posts In This Thread:

On Wednesday, February 14, 2007 1:00 PM
JimMober wrote:

How can I remove hidden apostrophe in Excel?
Hi,

On Wednesday, February 14, 2007 1:08 PM
Nozza wrote:

Re: How can I remove hidden apostrophe in Excel?

On Wed, 14 Feb 2007 10:00:38 -0800, Jim Moberg
<JimM...@discussions.microsoft.com> wrote:

If the cells are all numbers, then add 0 to them in a new column.

This will convert the string 7 to a numeric 7.

eg If A1 is equal to '7 then in cell B1 use the formula =A1+0

HTH

Noz
--
Email (ROT13)
abmmn_...@lnubb.pb.hx

On Wednesday, February 14, 2007 1:12 PM
Bill Ridgeway wrote:

The ' formats the cell to align left.
The ' formats the cell to align left. You may also see carat ^ (not sure
about correct spelling) which centres text and " which aligns text to the
right. You can't delete it and there's nothing to worry about.

Regards.

Bill Ridgeway
Computer Solutions

"Jim Moberg" <JimM...@discussions.microsoft.com> wrote in message
news:04862269-5D4D-4C77...@microsoft.com...

On Wednesday, February 14, 2007 1:13 PM
Gord Dibben wrote:

Is the apostrophe visible only in the formula bar?
Is the apostrophe visible only in the formula bar?

The CLEAN function works for me.

=CLEAN(A1) then copy>paste special>values>ok>esc.

Another thought, if you have Tools>Options>Transition>Transition Navigation Keys
options checkmarked, you could be seeing the Lotus alignment mark.

' for left aligned
^ for centered
" for right

Gord Dibben MS Excel MVP

On Wed, 14 Feb 2007 10:00:38 -0800, Jim Moberg
<JimM...@discussions.microsoft.com> wrote:

On Wednesday, February 14, 2007 1:41 PM
JimMober wrote:

Re: How can I remove hidden apostrophe in Excel?
I did try that and it did not work for me.

"Gord Dibben" wrote:

On Wednesday, February 14, 2007 1:43 PM
JimMober wrote:

It looks like I found the solution.
It looks like I found the solution. I saved the file as a csv file type and

after I brought it into excel again I did not see the apostrophe.

"Jim Moberg" wrote:

On Wednesday, February 14, 2007 1:54 PM
Dave Peterson wrote:

Is a macro ok?
Is a macro ok?

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constants in selection!"
Exit Sub
End If

For Each myCell In myRng.Cells
If myCell.PrefixCharacter <> "'" Then
myCell.Value = "'" & myCell.Text
End If
Next myCell

End Sub

Select a range and try it out.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Jim Moberg wrote:

--

Dave Peterson

On Wednesday, February 14, 2007 4:19 PM
Lori wrote:

You could also try selecting a column and then Data > Text to columnsOn Feb

You could also try selecting a column and then Data > Text to columns

wrote:

On Wednesday, May 28, 2008 10:36 PM
Karen Ellis wrote:

remove hidden apostrophe in Excel

This formula worked like a charm for me:
=VALUE(cell containing apostrophe)
Then you can copy, paste special, and click Values to replace the cells containing apostrophes.

On Thursday, May 29, 2008 2:55 AM
DILipande wrote:

"unknown" wrote:

On Thursday, May 29, 2008 5:10 PM

Rick Rothstein \(MVP - VB\) wrote:

You can remove the apostrophes from numerical values directly.
You can remove the apostrophes from numerical values directly. Select the
cells in column, click Data/Text To Columns on Excel's menu bar and then
click the Finish button. If you have more than one column with your "text
numbers", then you will have to do the above column-by-column one-at-a-time.

Rick

"Karen Ellis" wrote in message news:20085282236...@cox.net...

On Monday, October 27, 2008 11:10 PM
Bill Crighton wrote:

removing the "hidden apostrophe" from cells in excel.

There may be better methods but my fix is to copy a cell that does not have the apostrophe, then paste special the cells I want to remove the apostrophe from and select format. Thats it!

On Tuesday, October 28, 2008 3:00 AM
ShaneDevenshir wrote:

RE: removing the "hidden apostrophe" from cells in excel.
Hi,

"unknown" wrote:

On Tuesday, October 28, 2008 10:53 AM
RagDyeR wrote:

HTH,

On Thursday, June 25, 2009 1:57 PM
Tom McMillan wrote:

That is the wickest solution ever!
That is truly a genius solution... all hail!

On Thursday, April 08, 2010 4:48 PM
Jim Guertin wrote:

Turning a text formula to a numerical formula via macros
I create Excel formulas by adding together text strings and then I copy and paste special values the text formula resulting in the correct numerical formula except it is formated as text (i.e. it has that hidden apostrophe). I then edit the cell and delete the apostrophe, hit return and I have my formula. This type of formula construction can include text and numbers and calculations and anything else you want a formula to have.

However, I wanted to automate a spreadsheet via macros an dbuttons (VBA) and couldn't find a way to make a macro that would remove an apostrophe. Then I looked in this forum and found the ONLY thing that helped (I tried a bunch of them). It was the click on the cell and click Data then Text to columns. By doing the above while recording my key strokes I made a marco that works. Thank you for the idea.

Submitted via EggHeadCafe - Software Developer Portal of Choice
Assemblies in Folder Debug Build Checker
http://www.eggheadcafe.com/tutorials/aspnet/d7de5fe1-6155-4e81-96e1-9806fd69d760/assemblies-in-folder-debug-build-checker.aspx

Jesse Witt

unread,

Dec 7, 2010, 4:34:03 PM12/7/10

to

This was easy and worked great for both text and numbers. Thanks!

>>>> The CLEAN function works for me.
>>>>
>>>> =CLEAN(A1) then copy>paste special>values>ok>esc.
>>>>
>>>> Another thought, if you have Tools>Options>Transition>Transition Navigation Keys
>>>> options checkmarked, you could be seeing the Lotus alignment mark.
>>>>
>>>> ' for left aligned
>>>> ^ for centered
>>>> " for right
>>>>
>>>>
>>>> Gord Dibben MS Excel MVP
>>>>
>>>> On Wed, 14 Feb 2007 10:00:38 -0800, Jim Moberg
>>>> <JimM...@discussions.microsoft.com> wrote:

>>>>> On Wednesday, February 14, 2007 1:41 PM JimMober wrote:

>>>>> I did try that and it did not work for me.
>>>>>
>>>>> "Gord Dibben" wrote:

>>>>>> On Wednesday, February 14, 2007 1:43 PM JimMober wrote:

>>>>>> It looks like I found the solution. I saved the file as a csv file type and
>>>>>> after I brought it into excel again I did not see the apostrophe.
>>>>>>
>>>>>> "Jim Moberg" wrote:

>>>>>>> On Wednesday, February 14, 2007 1:54 PM Dave Peterson wrote:

>>>>>>> Is a macro ok?
>>>>>>>

>>>>>>>>>> After doing this you can have those value moved to right side of the cell and
>>>>>>>>>> you can see that hidden apostrophe no more is there in the cell. thanks
>>>>>>>>>>
>>>>>>>>>> --
>>>>>>>>>> Dilip Kumar Pandey
>>>>>>>>>> MBA, BCA, B.Com(Hons.)
>>>>>>>>>> dilip...@yahoo.com
>>>>>>>>>> dilip...@gmail.com
>>>>>>>>>> New Delhi, India
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> "unknown" wrote:

>>>>>>>>>>> On Thursday, May 29, 2008 5:10 PM Rick Rothstein \(MVP - VB\) wrote:

>>>>>>>>>>> You can remove the apostrophes from numerical values directly. Select the
>>>>>>>>>>> cells in column, click Data/Text To Columns on Excel's menu bar and then
>>>>>>>>>>> click the Finish button. If you have more than one column with your "text
>>>>>>>>>>> numbers", then you will have to do the above column-by-column one-at-a-time.
>>>>>>>>>>>
>>>>>>>>>>> Rick
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> "Karen Ellis" wrote in message news:20085282236...@cox.net...

>>>>>>>>>>>> On Monday, October 27, 2008 11:10 PM Bill Crighton wrote:

>>>>>>>>>>>> There may be better methods but my fix is to copy a cell that does not have the apostrophe, then paste special the cells I want to remove the apostrophe from and select format. Thats it!

>>>>>>>>>>>>>> There may be better methods but my fix is to copy a cell that does not have
>>>>>>>>>>>>>> the apostrophe, then paste special the cells I want to remove the apostrophe
>>>>>>>>>>>>>> from and select format. Thats it!

>>>>>>>>>>>>>>> On Thursday, June 25, 2009 1:57 PM Tom McMillan wrote:

>>>>>>>>>>>>>>> That is truly a genius solution... all hail!

>>>>>>>>>>>>>>>> On Thursday, April 08, 2010 4:48 PM Jim Guertin wrote:

>>>>>>>>>>>>>>>> I create Excel formulas by adding together text strings and then I copy and paste special values the text formula resulting in the correct numerical formula except it is formated as text (i.e. it has that hidden apostrophe). I then edit the cell and delete the apostrophe, hit return and I have my formula. This type of formula construction can include text and numbers and calculations and anything else you want a formula to have.
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>> However, I wanted to automate a spreadsheet via macros an dbuttons (VBA) and couldn't find a way to make a macro that would remove an apostrophe. Then I looked in this forum and found the ONLY thing that helped (I tried a bunch of them). It was the click on the cell and click Data then Text to columns. By doing the above while recording my key strokes I made a marco that works. Thank you for the idea.

>>>>>>>>>>>>>>>>> On Wednesday, August 04, 2010 7:43 PM Adrian Horth wrote:

>>>>>>>>>>>>>>>>> 1) Take one cell and delete the single quote mark.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> 2) Using the format painter, select this cell.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> 3) Apply the format to all other cells.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> Done.
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> Cheers,
>>>>>>>>>>>>>>>>>
>>>>>>>>>>>>>>>>> Adrian

>>>>>>>>>>>>>>>>> Submitted via EggHeadCafe
>>>>>>>>>>>>>>>>> Microsoft ASP.NET For Beginners
>>>>>>>>>>>>>>>>> http://www.eggheadcafe.com/training-topic-area/ASP-NET/7/ASP.aspx

Jesse Witt

unread,

Dec 7, 2010, 4:35:04 PM12/7/10