How can I remove hidden apostrophe in Excel?

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!

How can I remove hidden apostrophe in Excel?

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

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


Gord Dibben  MS Excel MVP

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

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

How can I remove hidden apostrophe in Excel?

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

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.

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


--

Dave Peterson

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

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.

Hi, you can multiply those value by 1 which containes hidden apostrophe.

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

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

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!

removing the "hidden apostrophe" from cells in excel.

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

Can't see the entire thread here, but ...

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!

That is the wickest solution ever!

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

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.

The Simple Way is often the best

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

removing the "hidden apostrophe" from cells in excel.

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

removing the "hidden apostrophe" from cells in excel.

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

removing the "hidden apostrophe" from cells in excel.

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

removing the "hidden apostrophe" from cells in excel.

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

removing the "hidden apostrophe" from cells in excel.

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

removing the "hidden apostrophe" from cells in excel.

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

The Simple Way is often the best

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

removing the "hidden apostrophe" from cells in excel.

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!

removing the "hidden apostrophe" from cells in excel.

Bill Crighton,



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



Best solution found so far.