Replace/Substitute Text with Varied Text String Lengths

[jessitarexcel]

Hello,

I have over 802 formulas that I need to update with a different suburb for each formula. This is the formula:

=IFERROR(GETPIVOTDATA("Count of Works Commenced Date",'SAG Pivot Tables - Locked'!$EV$3,"Suburb","Acacia Ridge","LGA","Brisbane City Council"),"0")

I need to replace the following value: "Suburb","Acacia Ridge" - therefore I need to update the suburb in each formula. I can do this one by one but there must be a better way. I am mucking around with Power Query and also the replace/substitute formula, however, as the suburb names vary in length, this is obviously not going to work so easily.

This is how the data is arranged:

LGA​	Suburb​	Works Commenced​	Works Completed​
BCC	Acacia Ridge	=IFERROR(GETPIVOTDATA("Count of Works Commenced Date",'SAG Pivot Tables - Locked'!$EV$3,"Suburb","Acacia Ridge","LGA","Brisbane City Council"),"0")	=IFERROR(GETPIVOTDATA("Count of Works Completed",'SAG Pivot Tables - Locked'!$EV$3,"Suburb","Acacia Ridge","LGA","Brisbane City Council"),"0")
LCC	Berrinba	=IFERROR(GETPIVOTDATA("Count of Works Commenced Date",'SAG Pivot Tables - Locked'!$EV$3,"Suburb","Berrinba","LGA","Logan City Council"),"0")	=IFERROR(GETPIVOTDATA("Count of Works Completed",'SAG Pivot Tables - Locked'!$EV$3,"Suburb","Berrinba","LGA","Logan City Council"),"0")

Firstly, may I ask, is this possible in Power Query? If not, is there an adjustment I can make to a formula to take into account the varying text string lengths? I have only been given a very short timeframe to deliver this report and it will take the whole day to type and replace each of the suburbs one by one. I am sure there must be a better way than the methods I have already tried.

 

[Alex Blakenburg]

You could do the whole thing in Power Query, you wouldn't use PQ just do a final replace.

Using GetPivot you would normally structure it more like this:
(You haven't provided enough data to test it or just the correct cell references)

Book3
	A	B	C	D	E
9	LGA Full	LGA	Suburb	Works Commenced	Works Completed
10	Brisbane City Council	BCC	Acacia Ridge	0	0
11	Logan City Council	LCC	Berrinba	0	0
Sheet1

Cell Formulas
Range	Formula
D10:D11	D10	=IFERROR(GETPIVOTDATA("Count of Works Commenced Date",'SAG Pivot Tables - Locked'!$EV$3,"Suburb",C10,"LGA",A10),"0")
E10:E11	E10	=IFERROR(GETPIVOTDATA("Count of Works Completed",'SAG Pivot Tables - Locked'!$EV$3,"Suburb",C10,"LGA",A10),"0")

 

[jessitarexcel]

Oh I feel so silly. That is so simple. I was overthinking the solution. I will just rewrite the formula to refer to the table values instead of names.

 

[jessitarexcel]

I now have it working. I just had a quotation mark around the cell reference and that is why it was not working. All good now. Thank you so much.

 

[Alex Blakenburg]

You're welcome. Glad I could help.