Thread: Can I extract text as a value from a formula?

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

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


"Amy O" wrote:

> 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

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

willwest22@yahoo.com

"Amy O" <Amy O@discussions.microsoft.com> wrote in message
news:53C33569-1C09-44EE-927E-85C7FE9F54E8@microsoft.com...
| 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