Time formated as TEXT 3:27PM

[MDuff]

I have some (well a lot) of infromation some one sent me in excel where the times are stored as 3:27PM not 3:27 AM issue is as they are stored as text and not time or number format

I have come up with this work around Useing 2 formulas but I was wondering if any one can come up with one formula to fix it

=IF(LEN(I1107)<7,0&I1107,I1107) =ABS(LEFT(L1107,5)&" "&RIGHT(L1107,2))

thanks

 

[Brian from Maui]

You can use the Find/Replace function under the Edit menu

Find

?pm

and replace with

(space) pm

 

[Scott Huish]

Perhaps:

=IF(RIGHT(I107)="M",REPLACE(I107,LEN(I107)-1,0," "),I107)+0

Format cells as h:mm AM/PM

 

[MDuff]

Thanks to all!!!!