excelregexjscript

Get row and column from excel or sheet A1 notation using regex

I want to convert the excel cell range string to row and columnnames

If AS31 is input the expected output should be col = AS and row = 31

If 31 is input, then output is col = null and row = 31

If A is input, then output is col = A and row = null

I have tried with,

    const [, columnName, row] = "AAZ21".toUpperCase().match(/([A-Z]+)([0-9]+)/); console.log("col="+columnName + " row=" + row)

//Working correctly
//expected = col=AAZ row=21


    const [, columnName, row] = "21".toUpperCase().match(/([A-Z]+)([0-9]+)/); console.log("col="+columnName + " row=" + row)

//NOT Working correctly
//expected = col=null row=21


    const [, columnName, row] = "A".toUpperCase().match(/([A-Z]+)([0-9]+)/); console.log("col="+columnName + " row=" + row)

//NOT Working correctly
//expected = col=A row=null
Solution

  • You could get either the row or col.

    const
    getCell = s => ({ col: s.match(/\D+/)?.[0] || null, row: s.match(/\d+/)?.[0]  || null });

console.log(['AAZ21', '21', 'A'].map(getCell));