CODEWITHSUNDEEP
excelregexjscript
Code Guy
Code Guy

Reputation: 3198

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

Upvotes: 1

Views: 416

Answers (1)

Nina Scholz
Nina Scholz

Reputation: 386746

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));

Upvotes: 2

Related Questions