Converting Time from General Format HH:MM:SSam/pm to HH:MMA/P

[SeanG]

Can anyone help me convert these times to remove the seconds? Trying to use a vlookup to cross reference data sets and for the life of me I can't change the format of my 2nd data set's times due to what I think is the general format the export gave me. I've tried the format box's standard time formats as well as custom formats with no luck changing them at all....

An example of one time I need to change is: 02:19:09pm. I can't get the format to change at all everything I've tried such as concatenating left/right with =CONCATENATE(LEFT(J2,5)*RIGHT(J2,2)) is giving me value errors....

Thanks for any help you guys can provide,
Sean

 

[Pecoflyer]

If there is no space between the numbers and the "pm" part try =TIME(HOUR(SUBSTITUTE(J2,"am"," am")+0),MINUTE(SUBSTITUTE(J2,"am"," am")+0),0)

 

[Weazel]

maybe....

=REPLACE(J2,LOOKUP(2^15,SEARCH({"a","p"},J2)),0," ")+0 formatted as time

 

[SeanG]

These both worked! Thanks!!