I have tables with Start and End time data entered in either an AM/PM cell for each day in

Desired data

Challenges/variables:

1. Data is provided in free text so it may not be in a time format. Usually it is 1, 2, 4 or 5 characters but it might be anywhere from 1 -5 characters with some variations depending on the use of a colon. For example, 1, 10, 130, 1:30, 1130, 12:30.

My approach

IF(RIGHT(B2,2)="AM",text(IF(AND(LEN(C2)>0,LEN(C2)<3), 
C2&":00",IF(LEN(C2)>3, C2, "")), "HH:MM AM/PM"),
IF(RIGHT(B2,2)="PM",text(IF(AND(LEN(C2)>0,LEN(C2)<3), 12 C2&":00",IF(LEN(C2)>3,
12 split(C2,":")&":"&RIGHT(C2,2), "")), "HH:MM AM/PM"),""))

I used length to figure out if something was already in a time format, and convert those that weren't. I added 12 to any PM value. If it was blank, it would result in a blank space.

Limitations:

1. I couldnt get it to work as an array (not sure why)
2. I realized afterwards that this would not work for 3 or 4 characters where there was no colon Entries like 1120, 145 wont work, for example 7:00 - 1120 = 7:00.

Possible solutions? 1.I think probably a REGEX would work better to convert anything into a time format but other solutions would be possible such as seeing if the 3rd character from the Right was equal to : 2. As far as the reorganization, I think it will require some combination of filter, transpose, or query, but I think a prerequisite might be getting the conversion to work as an array of some sort.

Thanks in advance for your help. The shared spreadsheet is