CODEWITHSUNDEEP
c#exceloffice-interop
GustavoKnx
GustavoKnx

Reputation: 93

C# Replace leading apostrophe Excel COM

How can I remove leading apostrophe of a cell (or range) in Excel?

i.e.:

Cell value

Tried the following values but all of them doesn't include the leading apostrophe:

xlRange.Value
xlRange.Value2
xlRange.get_Value(Type.Missing)

Also tried using the replace code below but didn't work:

xlRange.Replace("'", "", Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByRows, true, Type.Missing, Type.Missing, Type.Missing);

xlRange.NumberFormat returns "General"

Upvotes: 2

Views: 713

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25663

The purpose of a leading apostrophe with numeric values in Excel is to mark the content as "text", which also aligns it to the left as a result. This can be seen by querying the cell content in the Immediate Window of the VBA Editor (Ctrl+G):

?ActiveCell.Value

If this is performed on text (with apostrophe) the result is flush at the left side of the window. When performed on a number a space precedes the result.

Converting the cell content to a number removes the apostrophe. Trying to do this to non-numeric content results in an error. The following code demonstrates how this can be done in VB-speak:

Sub ConvertLeftAlignedNumber()
    Dim rng As Excel.Range
    Dim rngVal As Variant

    Set rng = xlApp.ActiveCell
    rngVal = rng.Value
    If IsNumeric(rngVal) Then
        rng.Value = Val(rngVal)
    End If
End Sub

In C# use Double.TryParse() instead of IsNumeric

If the problem is also to remove leading apostrophes from text as well as numbers this can be done for text values by assiging the text content to a variable, clearing the cell, then writing the value back.

    //test data
    Excel.Range rngString = ws.get_Range("B12", missing);        
    Excel.Range rngLeftValue = ws.get_Range("D14", missing);        
    Excel.Range rngValue = ws.get_Range("A15", missing);
    rngString.Value2 = "'test";
    rngLeftValue.Value2 = "'10";
    rngValue.Value2 = 10;

    //Is numeric?
    Double val;
    if (Double.TryParse(rngString.Value2.ToString(), out val))
    {
        System.Diagnostics.Debug.Print(val.ToString());
    }
    else //it's a string
    {
        string sString = rngString.Text.ToString();
        rngString.Clear();
        string sStringx = sString.Substring(0);
        rngString.Value2 = sStringx;
        System.Diagnostics.Debug.Print(rngString.Value2.ToString());
    }
    if (Double.TryParse(rngLeftValue.Value2.ToString(), out val))
    {
        System.Diagnostics.Debug.Print(val.ToString());
    }
    if (Double.TryParse(rngValue.Value2.ToString(), out val))
    {
        System.Diagnostics.Debug.Print(val.ToString());
    }

In VB(A) the Right function can be used:

Dim s As String

s = CStr(ActiveCell.Value2)
ActiveCell.Clear
ActiveCell.Value2 = Right(s, 1)

Upvotes: 1

Related Questions