change lookup value to text

[ZTB]

I have the following formula which i want to change.

Match($A1,(reference lookup array)... to be changed to Match(Text($A1,0),(reference look up array).

The issue that i have around 1000 rows and more than 30 columns which have difference reference files. Any help is appreciated.

 

[aRandomHelper]

Try using replace? Ctrl + H in Excel

Search for: Match($A1,
replace with: Match(Text($A1,0),
click Options >>
uncheck entire cell contents
look in: Formulas

Replace All
Then change "A" to "B" and repeat, "B" to "C" and repeat, and so on.
If it's a 1 time process, you only need to do this for 30ish times which should take less than 2 minutes.

 

[ZTB]

Try using replace? Ctrl + H in Excel

Search for: Match($A1,
replace with: Match(Text($A1,0),
click Options >>
uncheck entire cell contents
look in: Formulas

Replace All
Then change "A" to "B" and repeat, "B" to "C" and repeat, and so on.
If it's a 1 time process, you only need to do this for 30ish times which should take less than 2 minutes.

The rows are from A1 to A1000, and the columns are linked to 30 different files so i am unable to replace that easily.

 

[aRandomHelper]

Maybe show a few columns of the entire formula, you can just censor off the file names if you think those are sensitive. There is definitely a way to replace.

 

[ZTB]

hi, this is a sample of what i have. The reference file is different for each of the 30 columns.

 

[aRandomHelper]

Ok try this... on a copy of your workbook first.

Step 1: change all formula to text. highlight all 30+ columns, and press Ctrl+H

Find: =​

Replace with: '=​

Step 2: replace MATCH with MATCH TEXT

Find: MATCH($​

Replace with: MATCH(TEXT($​

Step 3: replace MATCH with MATCH TEXT part 2

Find: ,'C:​

Replace with: ,0),'C:​

Step 4: this will be slightly more repetitive. do a text-to-columns on every of your 30+ columns, one at a time to convert them back into formulas

Select 1 column​

Press Alt + D + E (short cut), leave everything as default (delimited, tab, general) and just click Finish​

Select next column, repeat​

 

[Akuini]

Hi, @ZTB
Do you also have anything other than $A, like:
Match($B1,
Match($B2,
Match($C1,
...
etc

and if yes do you want to change it too?
I might have an idea by using macro.