Extract a specific portion of a text string in Excel?

[drawkcaB]

I'm positive this is possible to do, but I can't seem to figure out the right functions and order.

I want to extract a specific portion of a text, namely if I have the following in a cell as a result of using =CELL("filename",A1):

C:\Personal Files\[Filename Rev. CCC.xls]
or
C:\Personal Files\[Filename Rev. 23.xls]

I want to get a cell to have only the information between "Rev." and ".xls" (i.e. get CCC or 23 in the above examples). How would I accomplish this?

(As a side note, when I do =CELL("filename",A1) the filename has an extra space at the end. Any way to get rid of that?)

[Richard Buttrey]

Hi,

One way

Please Login or Register  to view this content.

HTH

[6StringJazzer]

Try this formula, where A1 is where you have your =CELL formula.

To eliminate extra blanks use the TRIM function.

=SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("Rev.",A1)-4),".xls]","")

[drawkcaB]

6StringerJazzer's formula did the trick. Richard's would have worked but I didn't supply the full path name. Since it has some other period in it it returned the wrong bit of information.

Thanks for the help from both of you!