Share this content
7

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

Find string text in formula and return result...

Didn't find your answer?

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 (7)

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)
Replying to lionofludesch:
avatar
By mung1
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)
Replying to lionofludesch:
avatar
By mung1
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
By paulwakefield1
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)
Replying to carnmores:
avatar
By mung1
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
By paulwakefield1
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