Copy Excel formula in a relative way I use NPOI to copy Excel formula, but can't find an option to do it in a relative way. Therefore, I've written a function to implement it. The task is "easy". When copying a formula A1+B1 of C1 to C2, the result will be A2+B2. But the formula can do more than that. For example:


Left(A1, 3) of B1 to B2 => Left(A2, 3)
(AB1 - AB$1) * $AB1 of AC1 to AD2 => (AC2 - AC$1) * $AB2
A1 &amp; "-B1-C1" of B1 to B2 => A2 &amp; "-B1-C1"


The difficult part is identifying the cell and handling the $.

public string GetCellForumulaRelative(string formula, int columnOffset, int rowOffset)
{
    var cells = formula.Split("+-*/(),:&amp;^&gt;=&lt; ".ToCharArray(), StringSplitOptions.RemoveEmptyEntries)
        // start w/ A-Z
        .Where(i =&gt; (Convert.ToChar(i.Substring(0, 1).ToUpperInvariant()) &gt;= 'A' &amp;&amp;
                     Convert.ToChar(i.Substring(0, 1).ToUpperInvariant()) &lt;= 'Z') ||
                     Convert.ToChar(i.Substring(0, 1).ToUpperInvariant()) == '$')
        // end w/ 0-9
        .Where(i =&gt; Convert.ToChar(i.Substring(i.Length - 1, 1)) &gt;= '0' &amp;&amp;
                    Convert.ToChar(i.Substring(i.Length - 1, 1)) &lt;= '9');

    int startIdx = 0;
    foreach (var cell in cells)
    {
        int sepIdx = cell.IndexOfAny("0123456789".ToCharArray());
        if (cell.Substring(sepIdx - 1, 1).Equals("$"))
            sepIdx--;

        string col = cell.Substring(0, sepIdx);
        if (col.StartsWith("$") == false)
            col = GetExcelColumnName(ExcelColumnNameToNumber(col) + columnOffset);

        string row = cell.Substring(sepIdx);
        if (row.StartsWith("$") == false)
            row = (Convert.ToInt32(row) + rowOffset).ToString();

        startIdx = formula.IndexOf(cell, startIdx);
        formula = formula.Substring(0, startIdx) +
                  col + row +
                  formula.Substring(startIdx + cell.Length);
        startIdx += cell.Length;
    }

    return formula;
}


Any suggestions will be appreciated: performance, improvements, one-liners, or bugs.
I haven't used NPOI and I would believe you have valid reasons for using it (I'll take a wild guess at saying the thing is running on a server that doesn't have Excel installed), but for the record, if my memory isn't failing me if you used Microsoft VSTO / Excel Interop, you could use a plain &amp; simple Copy+Paste and let Excel do the hard part.

Your code shows how much of a pain this could be otherwise.

That said @ANeves' comment about using Regular Expressions, along with @Zonko's comment about R1C1 references, could make your code much simpler:

Here's the magic regex: (\$?[A-Z]+\$?\d)(?=([^"]*"[^"]*")*[^"]*$)

...and the proof (using Expresso):



So the above regex pattern will spoonfeed you all A1 cell references that you need to analyze - those that aren't surrounded by quotes.

I don't know if NPOI facilitates this in any way (is there a Range object to play with?), but I would highly recommend getting the equivalent R1C1 cell references, so instead of AB$1 you get R1C[28]; then you can easily run a [much, much simpler] regex on these addresses to get the rows and columns and add your offsets, rebuild the addresses and rebuild the formulas.