const regex = new RegExp('\\b[A-Za-z2]+(?=\\()(?![^\\']*\\'!)', 'g')
const str = `=VLOOKUP(\$A9,'Summary'!\$A\$10:\$C\$30,3,FALSE)
=IFERROR((C10/B10),"N/A")
='New Chart Data (Date)'!L70
=IFERROR((C10/B10),Len(E30))
='New Chart Data(Date)'!L70 + (Len(5)&ROW())
=INDEX(C\$3:C\$12,MATCH(9^99,IF(MAX(IF(ABS(\$B\$3:\$B\$12-\$B18)=MIN(IF(\$A\$3:\$A\$12=\$A18,ABS(\$B\$3:\$B\$12-\$B18),999)),B\$3:B\$12))=IF(ABS(\$B\$3:\$B\$12-\$B18)=MIN(IF(\$A\$3:\$A\$12=\$A18,ABS(\$B\$3:\$B\$12-\$B18),999)),B\$3:B\$12),IF(ABS(\$B\$3:\$B\$12-\$B18)=MIN(IF(\$A\$3:\$A\$12=\$A18,ABS(\$B\$3:\$B\$12-\$B18),999)),C\$3:C\$12))))`;
const subst = `6`;
// The substituted value will be contained in the result variable
const result = str.replace(regex, subst);
console.log('Substitution result: ', result);
Please keep in mind that these code samples are automatically generated and are not guaranteed to work. If you find any syntax errors, feel free to submit a bug report. For a full regex reference for JavaScript, please visit: https://developer.mozilla.org/en/docs/Web/JavaScript/Guide/Regular_Expressions