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!

On Wed, 14 Feb 2007 10:00:38 -0800, Jim Moberg
<(E-Mail Removed)> wrote:

>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
--
Email (ROT13)
(E-Mail Removed)

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" <(E-Mail Removed)> wrote in message
news:04862269-5D4D-4C77-A532-(E-Mail Removed)...
> 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!

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
<(E-Mail Removed)> wrote:

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

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

"Gord Dibben" wrote:

> 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
> <(E-Mail Removed)> wrote:
>
> >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!
>
>

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.

"Jim Moberg" wrote:

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

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

--

Dave Peterson

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

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.
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 Removed)
(E-Mail Removed)
New Delhi, India


"unknown" wrote:

>