Insert/Concatenate Semicolon In A Number To Make It Time In Excel

In this post we will talk about how we could insert/concatenate colon “:” into numbers in excel to make it look like time value.

Assume we have data in column A which are actually time values but the colon has been wiped out from each of them and we want to make the values look like time value by inserting colon “:” in each of them.




We will see a simple excel formula called TEXT with the help of which we will be able to insert/concatenate colon into the numbers to make them look like time values.

Enter the formula =TEXT(A2,”#0\:00″) in the cell B2, so in this TEXT formula we are just changing the format of the numbers to make them look like they are time values without actually concatenating  colon between them.

Now drag the formula in the cell B2 till the place you have data available in column A.

As you could see in the formula , the second argument to the text function is the format in which you want the representation and I have provided the format string “#0\:00”) which will do the job of displaying the numbers as time values by inserting colons in between them.




You could also use the formula =–TEXT(A1,”#0\:00″) to make it a time value and change the format to hh:mm

Hope this helped.

Share The Knowledge

Random Posts

Leave a Reply