Regex to replace excel formula with sheet name

Hi i am trying to write a regex to replace occurrence of cell name in an excel formulae with a alias in place of sheet name. I am using Js XLS for parsing excel. ex :

 +AA74/AVERAGE('b'!Z40:AA40)

Output Required

 +a_AA74/AVERAGE(b_Z40:b_AA40)

current Output

 +a_AA74/AVERAGE(b_Z40:a_AA40)

where 'a' is the current sheet in which formulae is written and 'b' is the name of other sheet.I want to append sheet name before each cell name. But in this type of formula range formula should contain starting range sheet name.

Current Code I am using

var re = new RegExp("A.","g");
res = res.replace(re, "a_");
var re = new RegExp("A!","g");
res = res.replace(re, "a_");
var re = new RegExp("'B'!","g");
res = res.replace(re, "b_");
var re = new RegExp("'B'.","g");
res = res.replace(re, "b_");
res = res.replace(/\s/g,"");
res = res.replace(/(^|[^_A-Z])([A-Z]+\d+)/g, "$1"+'a_'+"$2");

Solution

You may use

var re = /^\+A!?|'b'!([A-Z]+\d+):([A-Z]+\d+)/g;
var s = "+AA74/AVERAGE('b'!Z40:AA40)";
var res = s.replace(re, function(m, g1, g2) {
  if (g1) {
  	return 'b_'+g1 + ":b_" + g2;
  } else return '+a_A';
});
console.log(res);

The ^\+A!?|'b'!([A-Z]+\d+):([A-Z]+\d+) regex matches:

^\+A!? - +A or +A! at the start of the string
| - or
'b'! - a sequence of literal chars
([A-Z]+\d+) - Group 1 capturing 1+ uppercase ASCII chars followed with 1+ digits
: - a colon
([A-Z]+\d+) - Group 2 capturing 1+ uppercase ASCII chars followed with 1+ digits

In the replacement, we check if the Group 1 matched (=participated in the match). If yes, we add b_ to the captured contents, if not, we just add a_ to A.