I need to convert the time in each column from text. Any help would be appreciated. The timevalue formula hasn't worked for me.View attachment Chats.xlsx
=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(TRIM(B2)," ","¬",4),FIND("¬",SUBSTITUTE(TRIM(B2)," ","¬",4))+1,99),"am", " am"),"pm"," pm"))
=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(TRIM(B2)," ","¬",4),FIND(" ",TRIM(B2))+1,FIND("¬",SUBSTITUTE(TRIM(B2)," ","¬",4))-FIND(" ",TRIM(B2))-1),"st",""),"nd",""),"rd",""),"th",""))
Obviously not.Would a Power Query solution be of any use?