How can I specify the correct column?

Question

I need to do different things based upon which field (column) the data is in.

            For Each row As DocumentFormat.OpenXml.Spreadsheet.Row In
                worksheet.Descendants(Of DocumentFormat.OpenXml.Spreadsheet.Row)
                    For Each cell As DocumentFormat.OpenXml.Spreadsheet.Cell In row
                        Select Case True
                            Case cell.CellReference.Value Like "C*"
                                'if this cell is in column C
                            Case cell.CellReference.Value Like "A*"
                                'if this cell is in column A
                            Case Else
                        End Select
                    Next
            Next

This works great as long as there are no more than 26 fields in a given spreadsheet.

How do I make sure that my Like "A*" doesn't react to column AA, AB, etc.?

Keep in mind that the OpenXML SDK always returns a full cell reference for the .cellreference.value, and not just the column. And I need to specify that I am not trying to wholesale throw out any column greater than 26, but I am trying to make sure I specify a specific column. It is possible that the column under scrutiny may end up being AA or AB, depending on the company that created the specific source sheet. I was hoping for a property, or barring that, how other people have learned to refer to specific columns in openxml.

Answer 1

Adding a # after the column letter(s) you are trying to match will tell the matcher that there must be a number after the letter. This will prevent your check for A matching if the column is actually AA.

Select Case True
    Case cell.CellReference.Value Like "C#*"
    'if this cell is in column C
    Case cell.CellReference.Value Like "A#*"
    'if this cell is in column A
    Case cell.CellReference.Value Like "AA#*"
    'if this cell is in column AA
    Case Else
End Select

From the documentation, the # will match "Any single digit (0–9)".