How do I replace formula text with required IDs depending upon sheet name and having IDs is in fixed-CodePudding

I have to replace formula text by required IDs(always in column B) depending upon sheet names referred, as example given below also Screenshot of excel attached herewith (required output is highlighted with yellow color)

eg. formula: =Node!A2 * Comp!A2
required output in new cell: [n1] * [c1]

attached screenshots,

EDIT - I have tried following things, 1] Added 1 button on Result sheet 2] On click of it called macro having code as below

Sub Button1_Click()

Dim formulaVal As String
Dim str1 As String
Dim str12 As String

formulaVal = Range("A2").Formula
Debug.Print formulaVal
'MsgBox Range("J2").Value2

str1 = Replace(formulaVal, "!A", "!B")
Debug.Print str1

'ActiveSheet.Range("E2").Formula = strl
ActiveSheet.Range("E2").Formula = "=Node!B2*Comp!B2"
'MsgBox Range("u2").Value
strl2 = Range("E2").Formula

Debug.Print strl2
End Sub

3] After button clicking getting required formula in cell but it is showing error data type mismatch as referred data is as string which is not compatible with formula

Please suggest alternate solution if any

CodePudding user response：

You can use this formula in B2 and pull it down:

="n"&VALUE(MID(FORMULATEXT(A2),8,FIND("*",FORMULATEXT(A2))-8))-1 & "*c" & VALUE(MID(FORMULATEXT(A2),FIND("Comp!A",FORMULATEXT(A2)) 6,10))-1

CodePudding user response：

If you have a Windows environment & Office 365 you could use the following:

=LET(split,FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(FORMULATEXT(A1),"=",""),"*","</b><b>")&"</b></a>","//b"),
        char,LOWER(LEFT(split,1)),
        row,MID(split,FIND("!A",split) 2,LEN(split))-1,
"="&TEXTJOIN("*",1,char&row))

FILTERXML() is used to split the text at each *. Thee result of the split is used to get the first Character (and change it to lower case) and to find the string behind !A, which is the row number (and substract 1 from it). Then TEXTJOIN() joins the calculated character and row number and places a * between each.

PS if A1 is an actual formula than replace A1 with FORMULATEXT(A1)

(More info on FILTERXML can be found in this post by JvdV: