Inserting Space In Cells

My question is about inserting spaces in a cell in Excel 2010.

The cells have times in them and they are in standard time with no space between the period. An example would be 2:00PM

I would like to know how I could add a space between the time and the period. I have already formatted the cells to transform standard time to military time, but the space is required to make that happen. I would not have a problem clicking on each cell and pressing the space bar, but there are over 16,000 cells.

I have done some research and I see that one method is using a code in VBA to automatically add the spaces in all of the cells, but I am unfamiliar with Excel programming.

The range of cells begins at B12, C12 and ends at B8326, C8326.

Thanks!

That was a perfect fix! Thanks!!

Originally Posted by nathansav

custom format it with your spaces, or in vba use Format

format(now, "hh mm ss") gives 16 27 01
format(now, "hh-mm-ss") gives 16-27-01

Thank you!

Originally Posted by buran

what do you have in the cells? is it proper excel time value (i.e. decimal number between 0 and 1) or text string?
If it is the former period is just a representation and even if you add space, it will reset to the same number format again. In this case just use custom number format to add space between number and the period.
If it is string then you can use standard find/replace functionality in excel. i.e. replace "PM" with " PM"