Parse Cell Location String into Row & Column

Question

i have string col= "AB21" which is an excel cell location.

I would like to parse it as string column = "AB" & int row = 21;

How can i do that?

Answer 1

If this was Excel I would rely on Excel since the definition of a valid range depends on the version. Unlike the previous answers, this routine will reject strings that look like ranges but are not valid ranges such as, with Excel 2003, "XB21" or "AB66000". I assume the function below can be converted to C#.

Sub SplitRangeStg(ByVal RngStg As String, ByRef RowNum As Long, _
                                          ByRef ColNum As Long)

  Dim RngErr As Boolean

  Err.Number = 0
  RngErr = False
  On Error Resume Next
  RowNum = Range(RngStg).Row      ' Sets Err.Number if range invalid
  If Err.Number <> 0 Then
    RngErr = True
    Err.Number = 0
  End If
  ColNum = Range(RngStg).Column   ' Sets Err.Number if range invalid
  If Err.Number <> 0 Then
    RngErr = True
    Err.Number = 0
  End If
  If RngErr Then
    RowNum = 0
    ColNum = 0
    Call MsgBox(RngStg & " not recognised as an Excel range", vbOKOnly)
  End If
  On Error GoTo 0

End Sub

Answer 2

string col = "AB21";
int startIndex = col.IndexOfAny("0123456789".ToCharArray());
string column = col.Substring(0, startIndex);
int row = Int32.Parse(col.Substring(startIndex));

Of course, you should guarantee that input string will be in correct format.

Answer 3

One option you have is to use a regex to validate and parse the input string. See the code below.

using System;
using System.Text.RegularExpressions;

namespace DemoRegExForStackOverflow
{
    class Program
    {
        static void Main(string[] args)
        {
            var regex = new Regex(@"(?<col>([A-Z]|[a-z])+)(?<row>(\d)+)");
            var input = @"AB12";
            var match = regex.Match(input);

            if( match != null )
            {
                var col = match.Groups["col"];
                var row = match.Groups["row"];

                Console.WriteLine("Input is: {0}", input);
                Console.WriteLine("Column is {0}", col.Value);
                Console.WriteLine("Row is {0}", row.Value);
             }
             else
             {
                 throw new ArgumentException("Invalid input");
             }
        }
    }
}