I need to replace columnNumber digit from a excel formula string.

I have tried writing a regex (/[A-Z]\d+/g) to get columnNumber with digit from the formula string which seems not working properly as It's just detecting columnNumber (E2~D2) instead of replacing only the digit of that columnNumber-digit it's replacing the whole columnNumber.

var formula = "=IF(E2<(D2*0.004),100,IF((100-(((E2-(D2*0.004))/(D2*0.004))*100))<0,0,(100-(((E2-(D2*0.004))/(D2*0.004))*100))))";
    var regex = /[A-Z]\d+/g;

    for(var i=2; i<=3; i++)
    console.log(formula.replace(regex,i)

I need this

/** 
=IF(E2<(D2*0.004),100,IF((100-(((E2-(D2*0.004))/(D2*0.004))*100))<0,0,(100-(((E2-(D2*0.004))/(D2*0.004))*100))))

=IF(E3<(D3*0.004),100,IF((100-(((E3-(D3*0.004))/(D3*0.004))*100))<0,0,(100-(((E3-(D3*0.004))/(D3*0.004))*100))))
**/

But getting this

/** 
=IF(2<(2*0.004),100,IF((100-(((2-(2*0.004))/(2*0.004))*100))<0,0,(100-(((2-(2*0.004))/(2*0.004))*100))))

=IF(3<(3*0.004),100,IF((100-(((3-(3*0.004))/(3*0.004))*100))<0,0,(100-(((3-(3*0.004))/(3*0.004))*100))))
**/

1 Answers

2
Emma On Best Solutions

I'm not quite sure, if you want to replace the digit after [A-Z] or not. If that would be the case, this might help you to do so:

var formula = "=IF(E2<(D2*0.004),100,IF((100-(((E2-(D2*0.004))/(D2*0.004))*100))<0,0,(100-(((E2-(D2*0.004))/(D2*0.004))*100))))";
    var regex = /([A-Z])\d+/g;

    for(var i=2; i<=3; i++){
        console.log(formula.replace(regex,'\$1'+i));
    }        

RegEx Demo

This graph shows how the expression would work:

enter image description here