Share this content
0
3049

Find string text in formula and return result in a formula?

Find string text in formula and return result...

Didn't find your answer?

Search AccountingWEB

This problem is having me scratching my head. I am not sure if there is a solution as a search on internet does not come up with anything close to an answer. So hopefully someone out there can show me a way or say "give up - there is no way".

I have a IFERROR, INDEX, MATCH formula that finds and returns values from other workbooks. The other source workbooks are named Site01, Site02 and so on. The results are listed in a column so row 1 will return values from Site01, row 2 will return Site02 and so on. So in the formula it will refer to ..."Site01.xlsm" (twice).

Is there a formula (to place next to these results) that will search the said IFERROR, INDEX, MATCH formula and return the source workbook name i.e. Site01 , Site02 and so on? I am assuming there may be a generic way to search a given text in a formula or a specific way to return a workbook name.

Please note I am searching a formula not a text.

Any guidance is greatly appreciated.

Thanks!

Replies

Please login or register to join the discussion.

avatar
By ACDWebb
01st Mar 2014 14:17

So your formula contains Site01.xlsm

 not the returned value?

Can you therefore not just use

MID(address of cell with formula,FIND("filename to find",address of cell with formula,1),LEN("filename to find"))

Or perhaps I have misunderstood

 

Thanks (0)
avatar
By mung1
to lionofludesch
01st Mar 2014 17:20

returns #VALUE

Thank you ADCWebb. Unfortunately your suggestion returns #VALUE!.

Also (and I realise I did not explain this very well), I need the result to return the file name and there are 65 different file names in the column, which would mean writing a formula for each file name find ie Site01, Site02 etc,. So would not be possible to copy the FIND formula all the way down the 65 rows.

I hope this makes sense?

Thanks (0)
avatar
By ACDWebb
02nd Mar 2014 09:27

Because of course

it is searching the returned result not the formula ... silly me.

Does THIS article help in any way?

Thanks (0)
avatar
By mung1
to lionofludesch
19th Mar 2014 08:57

will try Excel 2013

Hi ACDWebb

Apologies for not responding sooner.

Another respondent has suggested using Excel 2013. So I have upgraded and this resolves. Just leaves writing the search/find formula but I should be able to work that out.

Many thanks

 

 

 

Thanks (0)
avatar
03rd Mar 2014 09:26

If you have Excel 2013

use FORMULATEXT to return the formula as text which you can then search using the normal text functions. Otherwise a VBA solution is probably the best bet.

Thanks (0)
avatar
By mung1
to carnmores
19th Mar 2014 08:59

Response

Paul

Apologies for my slow feedback.

I have now upgraded to Excel 2013 and =formulatext works. Many thanks for the tip.

Thanks (0)
avatar
03rd Mar 2014 09:35

Another thought

ASAP Utilities has a function ASAPGETFORMULA which will also return the formula as text.

ASAP Utilities is an (in my view, excellent) addon from here: http://www.asap-utilities.com/

However it is not free for business use.

Thanks (0)
Share this content