Can I extract text as a value from a formula?

[Guest]

I have a column with the following formula:
='\\Ourcompany\studydata\P_03\Lab\Manifest\Round 2\[Shipment 18 10
4.xls]Sheet1'!H18

and would like to extract the workbook location from it. Is there a formula
to do this? Thanks.
Amy

 

[Guest]

Amy,
By the workbook location, you mean everything before the start of the File
Name but not including the equal sign or the single quote?
In other words, this:
\\Ourcompany\studydata\P_03\Lab\Manifest\Round 2\

If this is correct . . .
If this data is in cell A5, how about:
=MID(A5,FIND("'",A5,1)+1,(FIND("[",A5,1)-FIND("'",A5,1))-1)

tj

 

[William]

Hi Amy - this may get you started but please note that all the workbooks
which the formulae relate to should be closed.

Place the following into a general module

Function showformul(c As Range)
Application.Volatile
'The following is all one line if the text wraps.
showformul = Left(Right(c.Formula, Len(c.Formula) - 1),
Application.Find("[", Right(c.Formula, Len(c.Formula) - 1)) - 2)
End Function

To return the path from a formula contained in, say, cell C3, use
=showformul(C3)
--
XL2002
Regards

William

(e-mail address removed)

| I have a column with the following formula:
| ='\\Ourcompany\studydata\P_03\Lab\Manifest\Round 2\[Shipment 18 10
| 4.xls]Sheet1'!H18
|
| and would like to extract the workbook location from it. Is there a
formula
| to do this? Thanks.
| Amy