Sometimes we are given a column of times in a worksheet but they don’t look formatted as we went them to be, e.g. 1400 instead of 14:00.
If the times are as shown in column A above, we need to find the length of each “time”to be able to put the colon in the right place. If the length is 4, we need to put a colon after the first two numbers, if the length is 3, we need to put the colon after the first number, i.e. we need to subtract two from the length of the “time”.
The LEFT function takes the specified number of characters from the left of a string, in this case the length of the string -2. The LEN function allows us to find the length of the string. The & is our joining operator and the colon has to be enclosed in double quotes as we are creating a text string. Finally we need the RIGHT function to give us the last two characters from the string.