How can I remove hidden apostrophe in Excel?

[Jim Moberg]

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]

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!

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

 

[Bill Ridgeway]

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

 

[Gord Dibben]

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

 

[Jim Moberg]

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

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

 

[Jim Moberg]

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]

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

 

[Lori]

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

 

[Karen Ellis]

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]

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.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[Rick Rothstein \(MVP - VB\)]

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


in message news:[email protected]...

 

[Bill Crighton]

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]

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.

 

[RagDyeR]

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

 

[David Whang]

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!

 

[David Whang]

Bill Crighton,

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

Best solution found so far.

 

[Klatuvarata]

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!

 

[boyde]

Seven year old post saved me a lot of time. Used the "=CLEAN" method and immediately solved my problem.

Thanks!!!