Insert leading zeros only when matching certain requierments

**godlev** · 09-02-2011, 04:20 AM

Hi all I'm new to this forum.
I tried to search for answer but nothing found....

i have to make reports every day and i receive raw timing data like in attachment screenshot raw-data.jpg

and i am trying to add double zero only to cells which start with :

also i want to add single leading zero to cells which which start with hours 1 to 9

so basically the transformed data should look like this screenshot

can you please help me find formula to do this transformation?

**PaulG2011** · 09-02-2011, 04:55 AM

Hi godlev,

I think you could use the FIND formula with a few IF statements here to do this.

If your data is in cell A1, you could enter a formula in cell B1 that says:

=IF(FIND(":",A1)=1,"00" & A1,IF(FIND(":",A1)=2,"0" & A1,A1))

This is saying:

If the position of a colon ( : ) is the first character in the string, then it means you need to add a "00". Otherwise if the colon is the second character in the string, you need to add a single "0". Otherwise, the colon is not the first or second character, which must mean that you start with a two digit value (like "12" or whatever), so just return whatever is in A1.

This will mean that when you copy the formula down, you will have a list of formatted values (which you could paste over the original data if you wanted).

If you wanted to write over the data "in-situ", you would need to use it doing visual basic macros, which is possible, but a step up from formulas. In this case you could make it so that you can the range of cells that you are trying to update, and the macro will scan each cell in the selection and see if it needs to be modified.

Anyway, hope this helps.

Paul

**Marcol** · 09-02-2011, 05:09 AM

Try this

=IF(ISNUMBER(A1),A1,IF(LEN(TRIM(A1))=6,"00"&TRIM(A1),"00"&TRIM(A1)&"0"))*1

Format the cells "hh:mm:ss"

This will allow for Excel possibly automatically recognising say 1:00:00 or 11:00:00 as a time.
It should also handle examples like :00:4 should they occur.

[EDIT]
This extention of Pauls' suggestion might be better

=IF(OR(A1="",ISNUMBER(A1)),A1,IF(FIND(":",A1)=1,"00" & A1,IF(FIND(":",A1)=2,"0" & A1,A1))*1)

This will handle numeric cells and blanks should they occur.

One thing to note my first suggestion will convert :00:4 to 00:00:40,
Pauls' formula will return 00:00:04

**godlev** · 09-02-2011, 05:25 AM

@PaulG2011 - BIG thank you for the swift reply - your formula works PERFECT!

@Marcol - thank you for your reply - your formula inserts single zero in front of the :

Case SOLVED!