Excel - How can I remove hidden apostrophe in Excel?

Asked By JimMober

14-Feb-07 01:00 PM

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!

14-Feb-07 01:08 PM

On Wed, 14 Feb 2007 10:00:38 -0800, Jim Moberg


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_jnyrf4@lnubb.pb.hx

14-Feb-07 01:13 PM

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


Gord Dibben  MS Excel MVP

On Wed, 14 Feb 2007 10:00:38 -0800, Jim Moberg

14-Feb-07 01:12 PM

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

14-Feb-07 01:41 PM

I did try that and it did not work for me.

14-Feb-07 01:43 PM

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.

14-Feb-07 01:54 PM

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

14-Feb-07 04:19 PM

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

28-May-08 10:36 PM

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.

29-May-08 02:55 AM

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.)
dilipandey@yahoo.com
dilipandey@gmail.com
New Delhi, India

29-May-08 05:10 PM

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

27-Oct-08 11:10 PM

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!

28-Oct-08 03:00 AM

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

28-Oct-08 10:53 AM

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

HTH,

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


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!

25-Jun-09 01:57 PM

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

08-Apr-10 04:48 PM

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.

04-Aug-10 07:43 PM

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

07-Dec-10 04:33 PM

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

07-Dec-10 04:34 PM

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

07-Dec-10 04:34 PM

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

07-Dec-10 04:39 PM

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

07-Dec-10 04:39 PM

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

07-Dec-10 04:48 PM

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

16-Jun-11 03:53 PM

The format painter worked for me......

17-Jan-12 01:44 PM

Bill Crighton,



i created an account just to tell you that the copy blank cell and paste special while keeping format is a genius idea.





THANK YOU SO MUCH!

17-Jan-12 01:45 PM

Bill Crighton,



I created an account JUST to tell you THANK YOU SO MUCH FOR YOUR AWESOME solution.



Best solution found so far.

13-Apr-12 10:43 AM

Well, I think this is probably even more simpler.



Excel --->Tools-->Options-->Transition-->Clear the Transition navigations keys box



Wala! No more hidden formatting chars!



Have a great day!