Index Match and Remove Part of Result String

[garifuna]

Hello,

I am trying to trim down the results of and index match. The results from my current formula have dates with time stamp information:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>2014-02-07 14:00:09 PST

I would like to remove everything after the space so the results look like this:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>2014-02-07

Here is the current formula in Column L of my spreadsheet.
=INDEX(H$2:H$1319,MATCH(A2,B$2:B$1319,0))

In Column M I have this formula to trim down the results, but would rather have it all in one to save space.
=LEFT(L2,FIND(",",L2)-1)

 

[Norie]

All you should need to do is replace L2 in the second formula with the INDEX/MATCH formula.

=LEFT(INDEX(H$2:H$1319,MATCH(A2,B$2:B$1319,0)),FIND(",",INDEX(H$2:H$1319,MATCH(A2,B$2:B$1319,0)))-1)

PS If the values you are looking up are 'real' date/time values you could try this to return the date.

=INT(INDEX(H$2:H$1319,MATCH(A2,B$2:B$1319,0)))

 

[popipipo]

=left([COLOR=#ff0000]index(h$2:h$1319,match(a2,b$2:b$1319,0))[/COLOR],find(",",[COLOR=#ff0000]index(h$2:h$1319,match(a2,b$2:b$1319,0))[/COLOR])-1)

 

[jtakw]

Hi,

First of all, I don't see a "comma" in your Sample "2014-02-07 14:00:09 PST", so I'm using a Space as delimiter.

Another way, result converted to Real Date value, Format result cell as Date of your choice:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
2	this	this						2014-02-07 14:00:09 PST				2/7/2014
Sheet673

Cell Formulas
Range	Formula
L2	=LEFT(SUBSTITUTE(INDEX(H$2:H$1322,MATCH(A2,B$2:B$1322,0))," ",REPT(" ",99)),99)+0

 

[garifuna]

All you should need to do is replace L2 in the second formula with the INDEX/MATCH formula.

=LEFT(INDEX(H$2:H$1319,MATCH(A2,B$2:B$1319,0)),FIND(",",INDEX(H$2:H$1319,MATCH(A2,B$2:B$1319,0)))-1)

PS If the values you are looking up are 'real' date/time values you could try this to return the date.

=INT(INDEX(H$2:H$1319,MATCH(A2,B$2:B$1319,0)))

This worked perfectly. Thank you for the help!