Find and replace

[peterwillmo]

I have a spreadsheet containing 4000+ lines of data created in Excel 2003 and am using the same Excel program to maintain the data on a windows 10 HP computer.

In the spreadsheet there is a column containing Time eg (1024 0916 1533) and want to change this format to 10:24 09:16 15:33. I have tried to select the columnand change the format and also tried find and replace a cell. Neither of thes seem to work the only result being the program crashes. Any sugestions please ?++Peter

 

[Peter_SSs]

How would you go about adding a semi colon in the below.

1024 0916 1533 to 10:24 09:16 15:33.

If there are no blank cells and each cell has 3 'times' in it

=REPLACE(REPLACE(REPLACE(A1,13,0,":"),8,0,":"),4,0,":")

If there are blanks or cells with a different number of times, you would need to give details and a representative set of sample data and expected results, not just one.

BTW, they are colons, not semicolons that you have shown inserted.

 

[billandrew]

Awesome Thank You

I believe the last Replace Start Number should be a 3 instead of 4.

confused with colon and semicolon

 

[Peter_SSs]

Awesome Thank You

I believe the last Replace Start Number should be a 3 instead of 4.

You're welcome. Yes, looks like I hit the wrong key with that 4. Glad you picked it up.