A Very stubborn apostrophe that only shows in editing mode and the formula bar...

I have an apostrophe in several cells that only shows when each cell is
double clicked on (bringing it into editing mode).

I have hundreds of cells with this "stubborn" apostrophe and
edit/replace ' with "nothing" doesn't see it. Again, it only shows up
when double clicked on and no other time. This data was imported from a
Mainframe application. Is there anything that a mainframe would export
that looks like an apostrophe but is not one and makes it stay hidden
in the cell until it is either double clicked on or viewed from the
formula bar?

Thanks in advance


--
duugg
------------------------------------------------------------------------
duugg's Profile: http://www.excelforum.com/member.php...o&userid=33372
View this thread: http://www.excelforum.com/showthread...hreadid=542617

This is a common problem- look at this post from Dave Peterson for a
solution:
http://groups.google.com/group/micro...15 da40bf621b

The leading apostrophe should be visible in the formula bar. It is a
different way of making material text than formatting the cell as text. If
you for any reason, dis-like these single quotes and want to remove them then
try:

Sub dont_quote_me()
Dim r As Range
For Each r In ActiveSheet.UsedRange
If r.PrefixCharacter = "'" Then
r.Value = r.Value
End If
Next
End Sub
--
Gary''s Student


"duugg" wrote:

>
> I have an apostrophe in several cells that only shows when each cell is
> double clicked on (bringing it into editing mode).
>
> I have hundreds of cells with this "stubborn" apostrophe and
> edit/replace ' with "nothing" doesn't see it. Again, it only shows up
> when double clicked on and no other time. This data was imported from a
> Mainframe application. Is there anything that a mainframe would export
> that looks like an apostrophe but is not one and makes it stay hidden
> in the cell until it is either double clicked on or viewed from the
> formula bar?
>
> Thanks in advance
>
>
> --
> duugg
> ------------------------------------------------------------------------
> duugg's Profile: http://www.excelforum.com/member.php...o&userid=33372
> View this thread: http://www.excelforum.com/showthread...hreadid=542617
>
>

A simpler method would be to enter a 1 into an empty cell. Copy that cell.
Then select your range of data to "fix". Select "Paste Special", and check
the box for "Multiply". This will remove all leading apostrophes. You can
then delete the 1 that you origianlly entered.

HTH,
Elkar


"Dave O" wrote:

> This is a common problem- look at this post from Dave Peterson for a
> solution:
> http://groups.google.com/group/micro...15 da40bf621b
>
>

That worked like a charm! My Wednesday has been made!

As grateful as I am that others give me macros/formulas, I don't know
what to do with them.

Actually, as I know I'll have to deal with using formula(s) like the
ones listed above your post, what do I do with them?

I literally mean, step by step.

For example:

After copying the formula, where exactly do I paste it to?
Do the cells have to be highlighted?
How do I "run" it?
Is there a "play formula" button?

I realize for you experts, execution of formulas you provide may be
second nature to you, but for us "programming newbies" it's not that
simple.

Thanks everyone.


--
duugg
------------------------------------------------------------------------
duugg's Profile: http://www.excelforum.com/member.php...o&userid=33372
View this thread: http://www.excelforum.com/showthread...hreadid=542617