How can I remove hidden apostrophe in Excel?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim Moberg - 14 Feb 2007 18:00 GMT

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!

Reply to this Message

Nozza - 14 Feb 2007 18:08 GMT

>Hi,
>
[quoted text clipped - 3 lines]
>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

Signature

Email (ROT13)
abmmn_jnyrf4@lnubb.pb.hx

Reply to this Message

Bill Ridgeway - 14 Feb 2007 18:12 GMT

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

> Hi,
>
[quoted text clipped - 6 lines]
> trim
> function in conjuction with the clean function and it didn't work. Ack!

Reply to this Message

Gord Dibben - 14 Feb 2007 18:13 GMT

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

>Hi,
>
[quoted text clipped - 3 lines]
>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!

Reply to this Message

Jim Moberg - 14 Feb 2007 18:41 GMT

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

> Is the apostrophe visible only in the formula bar?
>
[quoted text clipped - 18 lines]
> >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!

Reply to this Message

Jim Moberg - 14 Feb 2007 18:43 GMT

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.

> Hi,
>
[quoted text clipped - 3 lines]
> 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!

Reply to this Message

Dave Peterson - 14 Feb 2007 18:54 GMT

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

> Hi,
>
[quoted text clipped - 3 lines]
> 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!

Signature

Dave Peterson

Reply to this Message

Lori - 14 Feb 2007 21:19 GMT

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:

> Hi,
>
[quoted text clipped - 3 lines]
> 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!

Reply to this Message

Karen Ellis - 29 May 2008 03:36 GMT

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.

Reply to this Message

Rick Rothstein (MVP - VB) - 29 May 2008 22:10 GMT

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

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

Reply to this Message

DILipandey - 29 May 2008 07:55 GMT

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

Signature

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

"unknown" wrote:

Reply to this Message