Insert a Colon using an Excel Formula. Convert Text Time to Time Value. EMT 1753

Learn how to Insert a Colon using an Excel Formula. Use the REPLACE function and the LEN function. Convert Text Time to Time Value using a math operation.
(00:00) Introduction
(00:07) Use the REPLACE function and the LEN function to insert a colon in the variable poation for a time value.
(01:06) Convert Text Time to Time Value using a math operation.
(01:33) Form Decimals as Time Values using Number Formatting
(01:43) Summary

[Data_Mom]

Thanks Mike, really convenient to use REPLACE rather than LEFT, RIGHT, MID.

[GeertDelmulle]

That’s a nice little string of golden nuggets! :-)
(And a good reminder of “how to do insert” in Excel)
Thanks! :-)

[wayneedmondson]

Hi Mike. That's a fun one! The num_chars = 0 trick is awesome for inserting characters in a string. Thanks for demonstrating its use and usefulness in this quick EMT :)) Thumbs up!!

[drsteele]

Good use of REPLACE, Mike. I found a way to make it unnecessarily complex: =TRUNC(B3/100)&":"&TEXT(MOD(B3, 100), "00")

[CeliaAlvesSolveExcel]

Too much awesomeness in less than two minutes. 💪

[SyedMuzammilMahasanShahi]

Thanks Mike for this simple yet EXCELlent formula.

[excelrobot]

Love it! Length of zero, who woulda thunk!

[SimX]

Short and sweet! Very useful, thanks!

[sjn]

Whoo. Didn't know about that. Pretty sure I would have used left, right, len and a few "&". This is much better 👍.

[darrylmorgan]

Boom!Simply Awesome...Thank You Mike :)

[sasavienne]

You are simply fantastic..
Thanks Mike.
Salim

[TheSTEPHEN]

Sir requesting to create Shorts for such excel tips and tricks from you and Bill Sir as well!

[sachinrv]

Such small tricks go a long way in improving Excel skills. By the way there is also a trick to separate date and time mixed together using MOD function which you can make a video on. Thanks Mike. Cheers :)

[techyhimoni]

Here after Leila Gharani recommendation
Nice..

[edge]

just amazing Mike... I love the LEN(cell)-1

[BillSzysz]

Nice trick, Mike :-)))
Another one from me :-))) =--TEXT(B3, "00\:00")
Thanks for video!!!

[countduckula]

Thanks Mike - nice formula to do this.

[bagnon]

This is a VBA version I've used for years and only converts values where the range has time format:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Range

With Application
.EnableEvents = False
On Error Resume Next
For Each x In Target
If x.NumberFormat Like "*h:mm*" Then
If x <> Empty And x = Fix(x) Then
x = TimeSerial(Fix(x / 100), x - Fix(x / 100) * 100, 0)
End If
End If
Next
.EnableEvents = True
End With
End Sub

[hornavegur]

Hey Mike,

Is it possible to do it if you just have one column. In your case, if you only have column C. In case you dont have space to use an extra column for the number, in your case B.

Meaning that i write the number in row C, and then when i press enter, it appears with a colon in between the 2ns and 3rd digit?

When I try to do it it does not work because the formula refers to its own cell.

[pctLowBattery]

Is this only for the new excel? Or can like excel 2010 use this also?