I thought i would ask this in a new topic, i have imported data that displays like this.

Race 1 - 12:25PM Michael Stewart Plate (1100 METRES)

i am using this code to try and force it to 24 hour time.

=TEXT(TRIM(CLEAN(MID(LEFT(Q14,FIND("M",Q14)+1),FIND("-",Q14)+1,10))),"hh:mm:ss")

But having no luck, any ideas would be helpful.

__________________
One Drive

"If the corporates are treating you poorly , just go elsewhere."
"If they need you , they will soon find out."
"If you need them , you will soon find out." --moeee
_______________________________________________

There has to be an easier way of doing this (surely?!) but:

It's particularly lengthy so that it can handle X:XX and XX:XX format times. Tested with 9:30AM, 10:30AM, 1:30PM and 10:30PM, with the results you would expect.

There are undoubtedly better ways, the two necessary points are:
1 - the format needs to be "[H]:mm" for 24 hour time
2 - the string you send it needs to have a space between the time figures and the AM/PM for it to correctly recognise it

Thanks, and yes there is.

=TEXT(REPLACE(LEFT(Q14,FIND("M",Q14)-2),1,FIND("-",Q14)+1,"")+0.5*(MID(Q14,FIND("M",Q14)-1,1)="P"),"hh:mm")


Your's was a huge effort, i am not even going to try and understand what all that does,lol

I am, only good with less complex formulas.

__________________
One Drive

"If the corporates are treating you poorly , just go elsewhere."
"If they need you , they will soon find out."
"If you need them , you will soon find out." --moeee
_______________________________________________

How about text to columns, using delimiters, and find replace AM and then PM with a blank.
Then format that column special h:mm.

__________________
RaceCensus - powerful system testing software.
Now with over 409,000 Metropolitan, Provincial and Country races!
http://www.propun.com.au/horse_raci...ng_systems.html
*RaceCensus now updated to 31/10/2024
Video overview of RaceCensus here:
http://www.youtube.com/watch?v=W821YP_b0Pg