Merge date formats..... and more

[Bob Greene]

Please forgive my lack of memory, but I'm trying to do something I used to
do in Excel, and can't seem to get it right.

I get a text file (from a weather station), that is delimited thusly:

MM,DD, YY, HH:MM, Temp1, Value 1
MM,DD, YY, HH:MM, Temp 2, Value 2
MM,DD, YY, HH:MM, Temp 3, Value 3

I can do a format that will merge the MM DD YY into one column, formatted
MM/DD/YY, but I can't seem to come up with a way to merge the time into the
mix - what I want is a column formatted MM/DD/YY HH:MM.

And yes, I'd love to have the format be:
MM/DD/YY HH:MM | Value 1 | Value 2 | Value 3, but I think that will require
manual intervention - sort by temp col, then cut and paste.

Would appreciate any suggestions, solutions or comments.

Thanks.

 

[Peo Sjoblom]

One possible way not severely tested

=--(SUBSTITUTE(SUBSTITUTE(LEFT(TRIM(A1),FIND(":",TRIM(A1))+2),",","/"),"/","
",3))

format as mm/dd/yy h:mm