Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 "Amy O" <Amy wrote in message ... | 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenation formula loses text wrap formatting | Excel Discussion (Misc queries) | |||
formula is displaying as text rather than result. | Excel Worksheet Functions | |||
match cell text with text in formula | Excel Worksheet Functions | |||
Logical Text Formula | Excel Worksheet Functions | |||
Extract % from text string | Excel Worksheet Functions |