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]

If each of those times is in its own cell and is a text value, then try in an adjacent column a formula like
=TIME(LEFT(A1,2),RIGHT(A1,2),0)

If the times are actually numbers formatted to show any leading zero then try
=TIME(LEFT(TEXT(A1,"0000"),2),RIGHT(A1,2),0)

 

[Yongle]

Similar but different
=LEFT(TEXT(A1,"0000"),2)/24+RIGHT(A1,2)/(1440)

and format the range with custom format hh.mm

Cell can contain numbers or text

 

[peterwillmo]

Thanks to all who replied, but being a novice at excel I cannot get these formulas to work. Is it possible I could email a sample of the spreadsheet?

 

[Yongle]

Are the three values (1024 0916 1533) in different cells?
- we both assumed that there is only one value in each cell

You can test my method as follows:

In cell A1 enter this value
1024

In cell B1 enter formula
=TIME(LEFT(TEXT(A1,"0000"),2),RIGHT(A1,2),0)

Value returned = 0.4333333

Next right-click on cell B1 \ select Format Cells \ select Custom \ under "Type" enter hh:mm (the box probably contains the word General)

The value is now formated as a time 10:24

Now amend the value in A1 to your other values

Alternative solution
The formula provided by Peter_SSs makes Excel format the call automatically

 

[Peter_SSs]

Is it possible I could email a sample of the spreadsheet?

No, that is not permitted - see #4 of the Forum Rules.
However, you can post small screen shots directly in your post as I have done in this post. My signature block below has a link to help with that.

The first thing we need to determine (it has been mentioned a couple of times already) is whether the data is like column A below or column C (or something else)?

If it is like column C, is there always exactly 3 times in each cell or could there be 2 or 4 etc?

Book1
	A	B	C
1
2	1024		1024 0916 1533
3	0916
4	1533
Time

 

[peterwillmo]

Brilliant! it's working fine, thanks to all. I may have another one shortly!!

Best wishes,

Peter

 

[Peter_SSs]

Brilliant! it's working fine,

What is?

 

[billandrew]

Peter

If I may - How would you go about adding a semi colon in the below.

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

 

[peterwillmo]

The spreadsheet has 4000+ lines of data. So one or two is a simple task but 4000+ needs somthing else!
Regards,
Peter