Forum Discussion
Replace all formulas based on a pattern
I have several formulas in my worksheet that follow a similar pattern.
The formula is as follows.
=IFERROR(INDEX('Data Tab'!O:O,MATCH(TEXT(A350,"0"),'Data Tab'!E:E,0))*C350,0)
I want replace all of these formulas to exclude the TEXT function. The new formula should be as follows.
=IFERROR(INDEX('Data Tab'!O:O,MATCH(A350,'Data Tab'!E:E,0))*C350,0)
How can I achieve this using Find and Replace?
That depends on what else is in your workbook. The key is to make a 'Find' that you are sure is unique to only the cases you want to change. I assume the problem is that the A350 in this example may change but you still want to remove the TEXT. What about the column O:O just before the MATCH and the 'Data Tab'!... after the TEXT() function? will those change also?
Assuming the ",MATCH(TEXT(" portion is unique to what you want I would try:
FIND: ,MATCH(TEXT(
click FINDALL and see how many it finds
Then repeat with
FIND: ,"0"),
click FINDALL and see if that total matches the above.
If so then use those finds to replace:
,MATCH(TEXT( goes to ,MATCH(
,"0"), goes to ,
But if that doesn't work (i.e. there are many more cases of ,"0"), or both then maybe:
if you just want that formula to use a NUMBER instead of TEXT maybe force it back to be a number:
FIND: ,MATCH(TEXT(
REPLACE WITH: ,MATCH(--TEXT(
so it will convert to text and then convert back to a number, but if it wasn't a recognizable number then it will cause an error.
BTW the new excel has new functions like XMATCH and XLOOKUP that you might consider. For example that formula would likely simplify to
=XLOOKUP( A350, 'Data Tab'!E:E, 'Data Tab'!O:O, 0) * C350
2 Replies
That depends on what else is in your workbook. The key is to make a 'Find' that you are sure is unique to only the cases you want to change. I assume the problem is that the A350 in this example may change but you still want to remove the TEXT. What about the column O:O just before the MATCH and the 'Data Tab'!... after the TEXT() function? will those change also?
Assuming the ",MATCH(TEXT(" portion is unique to what you want I would try:
FIND: ,MATCH(TEXT(
click FINDALL and see how many it finds
Then repeat with
FIND: ,"0"),
click FINDALL and see if that total matches the above.
If so then use those finds to replace:
,MATCH(TEXT( goes to ,MATCH(
,"0"), goes to ,
But if that doesn't work (i.e. there are many more cases of ,"0"), or both then maybe:
if you just want that formula to use a NUMBER instead of TEXT maybe force it back to be a number:
FIND: ,MATCH(TEXT(
REPLACE WITH: ,MATCH(--TEXT(
so it will convert to text and then convert back to a number, but if it wasn't a recognizable number then it will cause an error.
BTW the new excel has new functions like XMATCH and XLOOKUP that you might consider. For example that formula would likely simplify to
=XLOOKUP( A350, 'Data Tab'!E:E, 'Data Tab'!O:O, 0) * C350
My issue is resolved.
I only wanted to change the formulas in a single worksheet. I had to replace all of the formulas with MATCH(TEXT( in them. I first performed a Replace All from
= to #=
Then I performed the 2 Replace Alls that you mentioned.
,MATCH(TEXT( goes to ,MATCH(
,"0"), goes to ,
Then I once again performed a Replace All from
#= to =
Thank you for your help.
