Spreadsheet reference conversions (Excel, ...)

//Utility code for converting spreadsheet style cell references (A1, D5, ...) to numeric row/column values and vice versa

//===================================================================================================================
// 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