Using TRIM, SUBSTITUTE & CLEAN

Good afternoon all. I've got a spreadsheet that I must manually reformat every three months because when I export it from a txt file it comes out pretty unusable.

Columns A-D are fine. Columns E, G, H and J are time, with am/pm formats. The others, columns K-S are hours and minutes. Apparently, in just about all of the columns there are empty spaces that need to be removed. In the time columns I can then do a quick replace and put a space between the last minute and the a or p to put it into the proper time format. Of course I'd use the custom number for this as well.

I was reading about using TRIM, SUBSTITUTE & CLEAN and I think this is what may work, but honestly, bot totally lost with how to do this. Can anyone help? Thanks for looking.

You can use this formula in V8 (for example):

=TIMEVALUE(LEFT(G8,LEN(G8)-1))+IF(RIGHT(G8)="p",0.5)

to change the value in G8 into a proper time (format the cell in the style you want). Then this can be copied across for the other columns, and then copied down as required. You can fix the values, then copy/paste over the original values.

Hope this helps.

Pete

Thanks for such a quick reply Pete. So after trying, I see that it didn't actually change the column itself, but then I can copy and paste special over the original column, ok, so now I'm reading the end of your sentence. Thanks so much. I appreciate it greatly!

Sue

Ron, great to know. I had been doing that with the a and the p, but did not know about the < for a space. Ah ha.... Yes, that will work, but if Pete's will do it all with a copy and paste, that might be quicker. Will try both. Thanks.