Extracting time in 3rd COL using Vlookup retrives an error

[KarbonEnk]

I need to extract the characters "to_" from an excel file in which can not be edited. The easy solution would be to do a find and replace on that file but since I can not edit it; I can't use that option. I'm using vLookup to retrieve the values from that excel sheet and when trying to do the extracting in the 3rd column of the lookup, Im running into a problem. Bellow is just an example:

1-04-13.xlsx Sheet 1

	A	B	C
1	Athelete	Shift
2	Janeiro, Jacqueline	2:15:00 PM	to 8:00:00 PM

<tbody>
</tbody>

Sheet 2

A	B	C
1	Athelete	Shift
2	=VLOOKUP('[1-04-13.xlsx]Sheet1'!A1,'[1-04-13.xlsx]Sheet1'!A1:C2,1,0)	=VLOOKUP('[1-04-13.xlsx]Sheet1'!A1,'[1-04-13.xlsx]Sheet1'!A1:C2,2,0)	How do I extract [1-04-13.xlsx]Sheet1!C2 to show: 8:00:00 PM

<tbody>
[TD="class: px"wysiwyg_dashes_td""][/TD]

</tbody>

I was thinking the mid function

=VLOOKUP('[1-04-13.xlsx]Sheet1'!A1,'[1-04-13.xlsx]Sheet1'!A1:C2,MID(COLUMN('[1-04-13.xslx]Sheet1'!$C$1),4,10),0)

Thank you in advance.

 

[JakeTheSnake3.0]

Use RIGHT and LEN

=RIGHT(C2, LEN(C2)-3)

 

[KarbonEnk]

Use RIGHT and LEN

=RIGHT(C2, LEN(C2)-3)

Thank you for your fast reply.

I might be out of my league but I don't know how to use that code inside of the vlookup function for the 3rd column

=VLOOKUP($A15,'[1-04-13.xlsx]Sheet1'!$A$7:$C$100,COLUMN(RIGHT('[1-04-13.xlsx]Sheet1'!$C$2,LEN('[1-04-13.xlsx]Sheet1'!$C$2)-3)),0)

 

[JakeTheSnake3.0]

Just take your regular VLOOKUP formula and replace any reference to the cell "C2" with what I gave you. Of course you'll replace "C2" with "[1-04-13.xlsx]Sheet1'!$C$2".

So my example would turn into

=RIGHT([1-04-13.xlsx]Sheet1'!$C$2, LEN([1-04-13.xlsx]Sheet1'!$C$2)-3)

 

[jarjarbingie]

Thank you for your fast reply.

I might be out of my league but I don't know how to use that code inside of the vlookup function for the 3rd column

=VLOOKUP($A15,'[1-04-13.xlsx]Sheet1'!$A$7:$C$100,COLUMN(RIGHT('[1-04-13.xlsx]Sheet1'!$C$2,LEN('[1-04-13.xlsx]Sheet1'!$C$2)-3)),0)

=RIGHT(C2, LEN(C2)-3)

change C2 with your VLOOKUP formula

 

[KarbonEnk]

=RIGHT(C2, LEN(C2)-3)

change C2 with your VLOOKUP formula

Thank you to both of you!!! I was getting a little overwhelmed but it works perfect now.

Time invested is time saved. I appreciate the time both of you gave me.

Final Result

=RIGHT(VLOOKUP($A15,'[1-04-13.xlsx]Sheet1'!$A$7:$C$100,COLUMN('[1-04-13.xlsx]Sheet1'!$C1),0),LEN(VLOOKUP($A15,'[1-04-13.xlsx]Sheet1'!$A$7:$C$100,COLUMN('[1-04-13.xlsx]Sheet1'!$C1),0))-3)