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

Question

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

Excel cannot do any magic. This looks impossible to me (especially when `IF` comes into the game). Unless you write an entire formula parser that can translate it into your formula language that you want as result. • There is no easy way, you will have to write a full praser for each Excel formula that can exist and if Excel adds new formulas in the future you will have to add them to your parser manually. This task is a way too much to ask for Stack Overflow. This is an entire project. — Pᴇʜ, May 24 '22 at 09:38
If it doesn't get more complex than these examples, then I guess you may pull this off with ms365's functions. However, I fully agree with the statement above. This question is way too broad. — JvdV, May 24 '22 at 09:49
thanks lot PEH for your comment, except **IF** is it possible for routine mathematical expression, as I have tried but getting error data type mismatch. — M Taj, May 24 '22 at 09:51
@MTaj no you would still need to write a parser to analyze the formula and find addresses to evaluate them and get the actual content of that address (to be able to replace the address in the formula). That's not an easy task neither and too broad to be answered here. — Pᴇʜ, May 25 '22 at 08:44

score 0 · Answer 1 · answered May 24 '22 at 07:09

0

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

answered May 24 '22 at 07:09

Pᴇʜ

56,719
10
49
73

Hi PEH, thanks lot for your answer. but here formula will not same all the time so I need generic solution for same. – M Taj May 24 '22 at 07:19
@MTaj Then please edit your original question and show more examples of your different formulas. Otherwise it is impossible to find a pattern for all of them. – Pᴇʜ May 24 '22 at 07:24
Hi PEH, I have edited question again please check out. – M Taj May 24 '22 at 08:03

P.b · Answer 2 · 2022-05-24T08:47:09.720

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: https://stackoverflow.com/a/61837697)

EDIT: I see the question was updated and this only provides an answer to part of the question now. Answer was based on :

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

2 Answers2