Excel Interop Range.Replace method not working

Question

I am trying to write a C# method that will remove all instances of a collection of characters from an Excel spreadsheet. Using the Range object's Replace method seems like the most efficient way to do this, rather than iterating through each cell. Here is what I'm working with:

Range extension method:

 public static void ReplaceChars(this Range me, String toReplace, String replacement = "") {

        //App is a static reference to the active Excel instance.
        //Alerts must be suppressed for characters that are not present in data.
        App.DisplayAlerts = false; 

        for (Int32 i = 0; i < toReplace.Length; i++) {
            me.Replace(

                //I'm using "Substring(i,1)" rather than "foreach (Char c in toReplace)" 
                //because Excel maps Char values differently than .NET.
                What: toReplace.Substring(i, 1),

                Replacement: replacement,
                LookAt: XlLookAt.xlPart,
                SearchOrder: XlSearchOrder.xlByRows,
                MatchCase: true,
                MatchByte: false, //I'm not 100% what this arg does
                SearchFormat: false, //Or this one
                ReplaceFormat: false); //Or this one
        }

        App.DisplayAlerts = true;
    }

Call from main program like this, for example, to leave only punctuation:

  App.ActiveSheet.Cells.ReplaceChars(
        "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890");

Sometimes 'U' will be not get replaced, sometimes 'T', sometimes all digits. I can't really predict it. If I comment out setting DisplayAlerts and put a breakpoint in the for loop, I will get alerts on the characters that are not replaced.

Has anyone had issues with Range.Replace like this? Am I just not assigning arguments correctly?

Answer 1

I think it all has to do will the NumberFormat property of the range in question. I added this before the replacements and it worked:

ws.Cells.NumberFormat = "@" //Set format to text
foreach (Range col in ws.Cells){ 
     //Iterate through columns rather than doing entire range to reduce memory overhead
     col.Value = col.Value //Flatten any lingering dates to text values
}