remove leading apostrophe in Excel 2010

Well, it works, but it completely clobbers the target cells formatting just like .clearformats.

I want to leave the formatting of the target cell untouched.

I tried changing  the xlPasteAll to xlPasteValues but that leaves prefixcharacter unchanged.

no points yet but keep trying.

rberke

by the way, as a workaround, I am temporarily using this code which saves some formatting, then restores it.  I could extend that technique, but there are dozens of different formats that I would have to code, so I really hate the approach.

                        Dim savStyle, savFormat
                        savStyle = cel.Style
                        savFormat = cel.NumberFormat
                        cel.ClearFormats
                        cel.Style = savStyle
                        cel.NumberFormat = savFormat

This is a known issue, and a pain in the behind for sure. This is the routine I use for it, although it's basically doing the same thing Martin posted. I use this because I can drop it anywhere (stored in my Personal.xlsb file), which uses the last cell (down one) to use as a temporary place to house the value.

Just call it (like the TestReplace() routine) and pass the range you want it to work on (the selection is what's used in the example)...

Sub TestReplace()
    Call ReplaceApostrophe2010(Selection)
End Sub

Sub ReplaceApostrophe2010(ByVal ReplaceCells As Range)
    Dim WS                      As Worksheet
    Dim rCell                       As Range
    Dim rLast                   As Range
    Dim vSET                    As Variant
    Set WS = ReplaceCells.Parent
    Set rLast = WS.Cells.SpecialCells(xlCellTypeLastCell).Offset(1)
    For Each rCell In ReplaceCells
        If rCell.PrefixCharacter = "'" And rCell.HasFormula = False Then
            vSET = rCell.Value
            rCell.Copy: rLast.PasteSpecial xlPasteFormats
            rCell.ClearContents
            rLast.Value = 1
            rLast.Copy: rCell.PasteSpecial xlPasteAll, xlPasteSpecialOperationMultiply
            rCell.Value = vSET
        End If
    Next rCell
    rLast.Delete xlUp
    Application.CutCopyMode = False
End Sub

Select allOpen in new window

EDIT: added portion to retain formatting.

HTH

Regards,
Zack Barresse

With Selection
    .Formula = "'test"  '  this turns on prefixcharacter.  I can't figure out how to turn it off
    '.Value = "test"     ' I think this worked in Excel 2003, but definitely does not in 2010
    Range("E1").Select
    Range("E1").Style = .Style
    Selection.Copy
    .PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
        False, Transpose:=False
    .Style = Range("E1").Style
    Range("E1").Style = "Normal"
    MsgBox .PrefixCharacter & .Formula

  Application.CutCopyMode = False

Select allOpen in new window

Well, it seems if you put the format back the apostrophe will follow it as well. I didn't know that. It's unfortunate to say the least.

What Martin provided will put the Style applied back on it, but not any additional formats. Would it be something where you could just use the Style? Or perhaps you could manually set the formats you want to put back on?

Zack

For an XML format workbook, you can edit the source XML but I'm not aware of any programmatic access to just remove the prefix character.

To all future responders:  I DO NOT WANT TO CLOBBER ANY FORMATTING !!!

All the solutions so far clobber the target cell formatting.

Zack,  I think you are finally getting that point.  

And, no, the "Style" is not good enough. TrimAllCells is a generic routine which I use at least 10 times a week on virtually any worksheet that is misbehaving.  It cleans up bunches of garbage, and never damages any formatting.  It even retains formatting in a single cell that has mixture of fonts and colors.

It worked fine in Excel 2003, but in Excel 2010 it does not remove the apostrophes.   My workaround "fixes" it, but now those trimmed cells lose various formats (fonts/borders/shading etc etc..)  

At the moment, I think I would have to extend this approach:
                        savStyle = cel.Style
                        savFormat = cel.NumberFormat

                        cel.ClearFormats

                        cel.Style = savStyle
                        cel.NumberFormat = savFormat

to incorporate dozens of other properties. For instance, just to preserve the fonts, I would have to save and replace  .Bold
 .colorIndex
 .FontStyle
 .Italic
 .Name
 .OutlineFont
 .Shadow
 .size
 .Strikethrough
 .Subscript
 .Superscript
 .Underline

You can preserve the formatting specifically like this...

Sub TestReplace()
    Call ReplaceApostrophe2010(Selection)
End Sub

Sub ReplaceApostrophe2010(ByVal ReplaceCells As Range)
    Dim WS                      As Worksheet
    Dim rCell                       As Range
    Dim rLast                   As Range
    Dim vSET                    As Variant
    Set WS = ReplaceCells.Parent
    Set rLast = WS.Cells.SpecialCells(xlCellTypeLastCell).Offset(1)
    For Each rCell In ReplaceCells
        If rCell.PrefixCharacter = "'" And rCell.HasFormula = False Then
            'Preserve specified formats
            rLast.Font.Name = rCell.Font.Name
            rLast.Font.Bold = rCell.Font.Bold
            rLast.Font.Italic = rCell.Font.Italic
            rLast.Font.Underline = rCell.Font.Underline
            rLast.Font.ColorIndex = rCell.Font.ColorIndex
            rLast.Font.Strikethrough = rCell.Font.Strikethrough
            rLast.Font.FontStyle = rCell.Font.FontStyle
            rLast.Font.Size = rCell.Font.Size
            rLast.Font.Subscript = rCell.Font.Subscript
            rLast.Font.Superscript = rCell.Font.Superscript
            rLast.Interior.Color = rCell.Interior.Color
            'Set value & update cell
            vSET = rCell.Value
            rCell.ClearContents
            rLast.Value = 1
            rLast.Copy: rCell.PasteSpecial xlPasteAll, xlPasteSpecialOperationMultiply
            rCell.Value = vSET
        End If
    Next rCell
    rLast.Delete xlUp
    Application.CutCopyMode = False
End Sub

Select allOpen in new window

I'm not sure what you mean by "OutlineFont" though.

HTH

by the way, the following simple test proves that Excel 2010 differs from Excel 2007 and Excel 2003

open new workbook and type 'test into any cell then hit Enter key.
In 2003b and 2010 you can return to the cell and use the backspace key to delete the apostrophe.  In 2010 you cannot.

@rberke: No, that is incorrect. You can manually delete this character from cells. The problem is doing so programatically.

Zack

It does not work manually on 2010.

I just tried it on Excel 2003, 2007 and 2010. I tried 2010 on two computers and it failed on both.

My post showed the screen shot as proof.

Have you tried a manual deletion on your version of Excel 2010?  

If your Excel 2010 allows manual deletion,  I would greatly appreciate your feedback, in particular what version of windows and what version of office and 32bit vs 64 bit?

Rberke.

Don't feel bad. I have been using 2010 for 5 months and just noticed this today.

But, this is a MAJOR pain in the backside.  What used to take exactly 1 line of very reliable code now takes several hundred lines of unreliable code.  It is unreliable because the sequence of the lines matters greatly:

Unreliable because this workaround fails:
                        savStyle = cel.Style
                        savFormat = cel.NumberFormat

                        cel.ClearFormats

                        cel.NumberFormat = savFormat   ' sequence is wrong
                        cel.Style = savStyle                       ' style should be applied first
                       

and this workaround succeeds.
                        savStyle = cel.Style
                        savFormat = cel.NumberFormat

                        cel.ClearFormats

                        cel.Style = savStyle
                        cel.NumberFormat = savFormat

Anyway, I will try to get Microsoft to admit it is a bug, but I cannot believe they ever will.
I'll leave this problem open a while, then PAQ it with my own workaround as the best answer.

I hear ya.

Re the above code, the reason it doesn't work is if the Style contains a number format, in which case a Style will overwrite those formats which are applied to the style. So your workaround of applying the style first would be the preferred way to go when working with any style which has a number format applied to it. To be on the safe side, and since it doesn't make a big difference, I'd just have it that way all the time.

Zack

I'll tell ya what, I will ask the Excel dev team about it. I know it was added to the cell format in 2010, but I'm not sure why. I'm sure I'll get a "this is by design" answer, but it'll be worth a shot. I think the reason we won't see a fix is there is no loss in fidelity, it's just super annoying.

MS tech support, bless their little hearts, are often times not able to move things up the ladder the way they should, or understand the merits of an argument. They do try though.

Zack

I hope you get some success.  Leading apostrophe is one of the first "tricks" people are taught at the beginning of their Excel careers. It is surprising that Microsoft could screw up something that is so fundamental.

Bob

I awarded points based upon how close the experts were to understanding the problem.
Unfortunately, there is no solution because Microsoft screwed things up starting in Excel 2010.

I am PAQing my own post in which I summarized that point.