I receive a text file that contains several "time" entry columns formatted as
text. The cells contain values such as "8:30AM" (no space between the zero
and "A"). I need to convert these to time formats so that I can subtract
multiple columns from one another.

The first thing I need to do is put a space between zero and "A" (or "P" for
PM) and I need to format the column as "hh:mm a/p". I have tried several
methods and none seem to work (I have thousands of cells to process). Text
to Columns is not an option because there is no delimiter and no exact fixed
width (for example: some entries are 7:00AM and other are 12:30PM).

What is the best (and simplest) way to achieve what I'm after?

I hope I've been clear, any help is greatly appreciated.

Thanks!