remove leading apostrophe in Excel 2010

I heavily use a vba program called TrimAllCells that nicely cleans up any spreadsheet.

Of course, the vba routine is supposed to leave the worksheet formatting intact.

The routine cannot seem to delete leading apostrophe's in Excel 2010.

The only workaround I have found is to clear all formats which my vba routine should NOT do.

The following vba code demonstrates the problem.
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
  MsgBox .PrefixCharacter & .formula
End With

rberke

p.s. by the way, this is not just a vba problem.  The same thing happens manually -- once you insert a leading apostrophe, it cannot be removed except by clearing the formats.
LVL 5
rberkeConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
This works.

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
   ' Select a blank cell anywhere on the sheet
    Range("E1").Select
    Selection.Copy
    .PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
        False, Transpose:=False
  MsgBox .PrefixCharacter & .Formula
  Application.CutCopyMode = False

End With

Open in new window


It's based on the second method mentioned in this article.
0
rberkeConsultantAuthor Commented:
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
0
Zack BarresseCEOCommented:
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

Open in new window


EDIT: added portion to retain formatting.

HTH

Regards,
Zack Barresse
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Martin LissOlder than dirtCommented:
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

Open in new window

0
Zack BarresseCEOCommented:
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
0
Rory ArchibaldCommented:
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.
0
rberkeConsultantAuthor Commented:
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
0
Zack BarresseCEOCommented:
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

Open in new window


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

HTH
0
rberkeConsultantAuthor Commented:
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.


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

Zack
0
rberkeConsultantAuthor Commented:
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.
0
Zack BarresseCEOCommented:
Have you tried a manual deletion on your version of Excel 2010?
I just tried it and stand very much corrected. I could've swore I'd done it before in 2010 and on. Same behavior in 2013.

Zack
0
rberkeConsultantAuthor Commented:
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.
0
Zack BarresseCEOCommented:
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
0
rberkeConsultantAuthor Commented:
I decided to waste my time and contact Microsoft Tech support.  The rep confirmed the behavior in 2010 and 2013, but didn't understand why I felt it was a problem -- after all the cell's visual display on the spreadsheet was correct and did not show the hidden prefix character, so why should I care if the formula bar shows a hidden prefix character or not?

I tried to explain it terms of the confusing visual feed back. Upon striking the delete key a user would immediately see the apostrophe disappear from the formula bar, so she would think the apostrophe was gone.  Then when she returned to the cell the next day, she would see it again and delete it again without realizing she was wasting her time.  In my case I have done this dozens of times without realizing what was happening.

I also explained that putting a number like 0023 into the field gives totally inconsistent results.  BOTH the formula bar and the worksheet cell show 23  without any indication that the cell has a hidden prefix character. But, later on, if the cell is changed to x0023, the pesky prefix character reappears in the formula bar.

I think this was all too subtle for the rep, but she politely agreed with me and said she would pass it on to the development team and even gave me a case #.   So of course I expect immediate action.  LOL.  I am sure it will be fixed by the time we get to Excel 2133 whereupon something else will be broken.

I have decided to abandon my attempts to save previous formatting, clear all formats, and restore previous formats.  That approach is fraught with potential problems and would almost certainly have to be modified for every future release of Excel.

Instead, I will simply ignore the problem.  In the past I regarded a spreadsheet with unnecessary prefix characters as "Unclean".  I will now simply treat them as "Normal".

Maybe in a year or two it will stop irritating me.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Zack BarresseCEOCommented:
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
0
rberkeConsultantAuthor Commented:
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
0
rberkeConsultantAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.