MID text formula

[jamilm]

Gurus,

i have a formula as text in one cell and i would like to add one more array through the mid function.


'=SUMPRODUCT(MONETARY_AMOUNT,--ISNUMBER(MATCH(FISCAL_YEAR,{2004},0)),1-ISNUMBER(MATCH(ACCOUNTING_PERIOD,{0,999,998},0)),--ISNUMBER(MATCH(LEFT(ACCOUNT&"#####",5),{"51005"},0)))*-1

'=SUMPRODUCT(MONETARY_AMOUNT,--ISNUMBER(MATCH(FISCAL_YEAR,{2004},0)),1-ISNUMBER(MATCH(ACCOUNTING_PERIOD,{0,999,998},0)),1-(FUND="KY004"),--ISNUMBER(MATCH(LEFT(ACCOUNT&"#####",5),{"51005"},0)))*-1


i know this is possible through the MID function, i somehow tried but it did not work.

grateful for your contribution.

 

[ukmikeb]

Hi

No no no, no formulas!

Use Excel's inbuilt find and replace.

Select the column/s where the formulas are, in the Find box put -
998},0)),
in the Replace box put -
998},0)),-1(FUND="ky004"),

Replace all.

May I suggest that you try it on one cell first to check that the change you make is correct and a valid formula then apply it to the larger range.

hth

 

[jamilm]

did not work. the replace in inbuilt did not allow.

Hi

No no no, no formulas!

Use Excel's inbuilt find and replace.

Select the column/s where the formulas are, in the Find box put -
998},0)),
in the Replace box put -
998},0)),-1(FUND="ky004"),

Replace all.

May I suggest that you try it on one cell first to check that the change you make is correct and a valid formula then apply it to the larger range.

hth

 

[jamilm]

i tried again the method of replace as you described and it worked this time.

thank you Mike for your help. very much appreciated.

Hi

No no no, no formulas!

Use Excel's inbuilt find and replace.

Select the column/s where the formulas are, in the Find box put -
998},0)),
in the Replace box put -
998},0)),-1(FUND="ky004"),

Replace all.

May I suggest that you try it on one cell first to check that the change you make is correct and a valid formula then apply it to the larger range.

hth

 

[ukmikeb]

You're welcome.

Thanks for the feedback.

Pleased to have helped solve your problem.

Good luck with your project.

 

[jamilm]

thanks again Mike.

your help saved lots of my time.

You're welcome.

Thanks for the feedback.

Pleased to have helped solve your problem.

Good luck with your project.