Adding : to imported numbers

[johnniec]

Hi,

Each day I import a series of times from an SQL database (imported via an
comma delimited file) and each time comes in as,

809 (really being 08:09am)
1012 ((really being 10:12am)
1236 (really being 12:36pm)
1755 ((really being 05:55pm)

etc, etc

At present what I now have to do is edit each individual number and insert
the " : " manually before Excel recognises it as a time format.

Can anyone offer up a formula which would add the " : " to each number 2
digits from the left.

I think thats fairly clear - please post back if its not clear enough.

Thanks many
John

 

[tracy]

Hi,

Try this:

If your first time 0809 is in cell A1...

=left(A1,2)&":"&right(A1,2)

you will have to bring in your file as a .txt in order to
use this formula, if you don't the leading zeros will be
dropped and the formula won't work if you just copy it
down. In other words, for the example above your answer
would read 80:9 instead of 08:09...try it and let me
know...thanks

 

[johnniec]

Thanks muchly Tracy - I'll try this at work the 'morrow and I'm pretty sure
it will do what I need.

Ta
John