I get an Excel spreadsheet with 5K race times that look like this:
18:29.3
18:29.7

I need to change them to look like this:
0:18:29
0:18:29

I have tried the find with this:
([0-9]{2}):([0-9]{2}).([0-9])

thinking I could then use a replace like this:
0:\1:\2

However, the find always comes back that no cells meet this criteria. I have
also tried a find with **:**.* and nothing is found. If this approach worked,
what would the replace string be?

I get hundreds of these times and an automated way of changing them would be
very helpful. Thanks in advance.

hi
is your first example formated as text?????
if not, then all you need to do is reformat.
your first example seems to be formated as mm:ss.0
change that to.......h:mm:ss
if it is text, use the value function to convert to number.
=value(a1) which will produce a value like .7703 or similar. then just
format as above.

regards
FSt1

"Deborah S" wrote:

I get an Excel spreadsheet with 5K race times that look like this:
18:29.3
18:29.7

I need to change them to look like this:
0:18:29
0:18:29

I have tried the find with this:
([0-9]{2}):([0-9]{2}).([0-9])

thinking I could then use a replace like this:
0:\1:\2

However, the find always comes back that no cells meet this criteria. I have
also tried a find with **:**.* and nothing is found. If this approach worked,
what would the replace string be?

I get hundreds of these times and an automated way of changing them would be
very helpful. Thanks in advance.

hi
post scriptum.
if your first example is text, try this instead of the value function.
enter a 1 in a cell off to the side and copy it. then high light the column
of text numbers (first example), then paste special multply. this should
convert the text numbers into numbers. format as desired.

regards
FSt1

"FSt1" wrote:

hi
is your first example formated as text?????
if not, then all you need to do is reformat.
your first example seems to be formated as mm:ss.0
change that to.......h:mm:ss
if it is text, use the value function to convert to number.
=value(a1) which will produce a value like .7703 or similar. then just
format as above.

regards
FSt1

"Deborah S" wrote:

I get an Excel spreadsheet with 5K race times that look like this:
18:29.3
18:29.7

I need to change them to look like this:
0:18:29
0:18:29

I have tried the find with this:
([0-9]{2}):([0-9]{2}).([0-9])

thinking I could then use a replace like this:
0:\1:\2

However, the find always comes back that no cells meet this criteria. I have
also tried a find with **:**.* and nothing is found. If this approach worked,
what would the replace string be?

I get hundreds of these times and an automated way of changing them would be
very helpful. Thanks in advance.

And if you just give the cells a custom format:

Format|cells|Number tab|custom category
h:mm:ss

What happens?

If they don't change, then some of your values aren't really times--they're text
masquerading as time/numbers.

I'd try:
Select the range
Change the number format to General (make sure it isn't Text)
Edit|Replace
what: : (colon)
with: : (colon)
replace all

It would be like reentering the values.

Then change the number format to that "pretty" one.

Deborah S wrote:

I get an Excel spreadsheet with 5K race times that look like this:
18:29.3
18:29.7

I need to change them to look like this:
0:18:29
0:18:29

I have tried the find with this:
([0-9]{2}):([0-9]{2}).([0-9])

thinking I could then use a replace like this:
0:\1:\2

However, the find always comes back that no cells meet this criteria. I have
also tried a find with **:**.* and nothing is found. If this approach worked,
what would the replace string be?

I get hundreds of these times and an automated way of changing them would be
very helpful. Thanks in advance.

--

Dave Peterson

Worked great - I just changed the format to Custom with hh.mm.ss. thank you
so much for your response ... this will save me tons of time.

"Dave Peterson" wrote:

And if you just give the cells a custom format:

Format|cells|Number tab|custom category
h:mm:ss

What happens?

If they don't change, then some of your values aren't really times--they're text
masquerading as time/numbers.

I'd try:
Select the range
Change the number format to General (make sure it isn't Text)
Edit|Replace
what: : (colon)
with: : (colon)
replace all

It would be like reentering the values.

Then change the number format to that "pretty" one.

Deborah S wrote:

I get an Excel spreadsheet with 5K race times that look like this:
18:29.3
18:29.7

I need to change them to look like this:
0:18:29
0:18:29

I have tried the find with this:
([0-9]{2}):([0-9]{2}).([0-9])

thinking I could then use a replace like this:
0:\1:\2

However, the find always comes back that no cells meet this criteria. I have
also tried a find with **:**.* and nothing is found. If this approach worked,
what would the replace string be?

I get hundreds of these times and an automated way of changing them would be
very helpful. Thanks in advance.

--

Dave Peterson