Excel Text Replace
 

I create documents every day for about 100 users and I am trying to figure
out how to modify a column. The column populates like this ToJo-8:05: or
ToJo-12:30.

The first 4 characters and time will be different for every person. I want
all the cells to look like the second example. So basically users created in
single digit hours, I have to manually go in and change them from ToJo-8:05:
to ToJo-08:05

Is there a formula or replace command I can use to make this change
automatically and not mess up the already correct ones (like ToJo-12:30)?

Thanks a bunch!!

Excel Text Replace
 

A couple of questions... Is the text part **always** four characters
followed by a dash? Is the colon at the end of the first example data item
actually there? Is so, was it supposed to be in the second example data item
also? Or is it a place-keeper of some sort for time values less than 10?

Rick


"Ken" wrote in message
...
I create documents every day for about 100 users and I am trying to figure
out how to modify a column. The column populates like this ToJo-8:05: or
ToJo-12:30.

The first 4 characters and time will be different for every person. I want
all the cells to look like the second example. So basically users created
in
single digit hours, I have to manually go in and change them from
ToJo-8:05:
to ToJo-08:05

Is there a formula or replace command I can use to make this change
automatically and not mess up the already correct ones (like ToJo-12:30)?

Thanks a bunch!!

Excel Text Replace
 

=LEFT(A1,5)&TEXT(MID(A1,6,5),"hh:mm")


"Ken" wrote:

I create documents every day for about 100 users and I am trying to figure
out how to modify a column. The column populates like this ToJo-8:05: or
ToJo-12:30.

The first 4 characters and time will be different for every person. I want
all the cells to look like the second example. So basically users created in
single digit hours, I have to manually go in and change them from ToJo-8:05:
to ToJo-08:05

Is there a formula or replace command I can use to make this change
automatically and not mess up the already correct ones (like ToJo-12:30)?

Thanks a bunch!!

Excel Text Replace
 

That works perfect, thanks teethless mama.
One more quick question, how can i run that formula down a column of users?
I'm sure there is an easier way then changing the "A1" to "B1" and so on...

"Teethless mama" wrote:

=LEFT(A1,5)&TEXT(MID(A1,6,5),"hh:mm")


"Ken" wrote:

I create documents every day for about 100 users and I am trying to figure
out how to modify a column. The column populates like this ToJo-8:05: or
ToJo-12:30.

The first 4 characters and time will be different for every person. I want
all the cells to look like the second example. So basically users created in
single digit hours, I have to manually go in and change them from ToJo-8:05:
to ToJo-08:05

Is there a formula or replace command I can use to make this change
automatically and not mess up the already correct ones (like ToJo-12:30)?

Thanks a bunch!!

Excel Text Replace
 

Nevermind!! I found my answer in another post!! Thanks for all your help!

"Ken" wrote:

That works perfect, thanks teethless mama.
One more quick question, how can i run that formula down a column of users?
I'm sure there is an easier way then changing the "A1" to "B1" and so on...

"Teethless mama" wrote:

=LEFT(A1,5)&TEXT(MID(A1,6,5),"hh:mm")


"Ken" wrote:

I create documents every day for about 100 users and I am trying to figure
out how to modify a column. The column populates like this ToJo-8:05: or
ToJo-12:30.

The first 4 characters and time will be different for every person. I want
all the cells to look like the second example. So basically users created in
single digit hours, I have to manually go in and change them from ToJo-8:05:
to ToJo-08:05

Is there a formula or replace command I can use to make this change
automatically and not mess up the already correct ones (like ToJo-12:30)?

Thanks a bunch!!