Spreadsheet reference conversions (Excel, ...)

//===================================================================================================================
// Function RCToCell()
// Created  :   2018-07-27
// Input        :   Row & Column Values
//  Requires    :  
// Returns  :   A String representing the Excel Cell reference corresponding to the input row/column
// Output       :  
//                 
// Change history
//  1.0.0       :   2018-07-27  : Initial version
//                      Converts an R1C1 spreadsheet cell reference into an alphanumeric string
//===================================================================================================================
Function/S RCToCell(Row,Column)
    Variable Row, Column                //Row and Column values, starting at 1
   

    Variable residue
    String Cell = ""

    do
        residue = mod(Column-1,26)                  //Find the remainder after dividing by 26 (letters)
        Cell = num2char(residue+65)     + Cell  //Convert remainder to letter using UTF-8 numbering
        Column = (Column - residue )/ 26            //Determine if we need another letter
    while (Column > 1)                                  // keep going while we need to add letters
   
    Cell = Cell + num2str(Row)                      //Add the row as a string
   
    Return Cell
   
End

//===================================================================================================================
// Function CellToRC()
// Created  :   2018-07-27
// Input        :   Excel type Cell reference as string, also passing output variables by reference
//  Requires    :  
// Returns  :   1 on success, negative values for error
// Output       :   Row & Column numbers corresponding to input cell are returned via reference passing
//                 
// Change history
//  1.0.0       :   2018-07-27  : Initial version
//                      Converts an Excel style spreadsheet cell reference into row and column values
//                      This code expects upper-case letters and will give incorrect results with lower case letters
//                      Input is matched to a pattern of letters followed by numbers
//  1.0.1       :   2018-07-30  : Added input checking to make sure that non-numeric portion is uppercase letters.
//                      Also checking that input has letters followed by numbers
//                      Code is somewhat forgiving of extra numbers/symbols before letters and letters/symbols after numbers
//                      Input like "Tabname!H258" will give a correct Column 8, Row 258 output (ignoring the TabName)
//                      However, absolute cell references ($AA$15) will fail due to the symbol between the letters and numbers
//===================================================================================================================
Function CellToRC(Cell,Row,Column)
    String Cell                     //The cell reference
    Variable &Row, &Column          //Row & Column values corresponding to cell, passed by reference so we can return them
   
    String Alpha, Numeric           //letter and number portions of the cell reference, in string format
   
    SplitString /E="([[:alpha:]]+)([[:digit:]]+)" Cell , Alpha, Numeric     //Extract letters and numbers using grep pattern
   
    //input checking, make sure we got both letters and numbers
    if(V_flag != 2)
        Print "Malformed Cell Reference."
        return -1           //return error code
    endif
   

    Row = str2num(Numeric)          //Convert row string to numeric value
   
    //Convert letter values to numbers
    //Treat this like a conversion from base 26, using A-Z as digits.
    Variable i = strlen(Alpha)              //determine how many digits we have
    Column = 0                                  //Initialize the numeric value
    Variable Letterval                          //Numeric value of each letter/digit
   
    do                                              //step through each digit backwards from the end
        //Find the UTF8 code for the current digit. Subtract 64 to get equivalent number value
        Letterval = char2num(Alpha[i-1])-64             //Convert the current letter digit into a value
        if(Letterval < 1 || Letterval > 26  )               //Check that it maps onto 1-26
            Print "Unexpected input value. Check that letters are uppercase."
            return -2           //return error code
        endif
        //Multiply by the correct base 26 exponent before adding to cumulative sum
        Column = Column + (Letterval * (26 ^ (strlen(Alpha) - i )))
        i = i - 1               //step to the next highest digit
    while(i > 0 )

//  Print Column, Row
   
    return 1            //signal successful execution
   
End