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

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

CodePudding user response：

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

CodePudding user response：

To handle the cases where either the column name or row number is not present in the input string, you can use the ? character in the regular expression to make the preceding group optional.

Here's an example of how you can modify your regular expression to handle these cases:

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

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

const [, columnName, row] = "A".toUpperCase().match(/([A-Z] )?([0-9] )?/);
console.log(`col=${columnName} row=${row}`);

The ? character makes the preceding group optional, so it will match either 0 or 1 occurrence of the preceding pattern. In this case, the regular expression will match either the column name (A-Z ) or the row number (0-9 ), or both, depending on what is present in the input string.