Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I replace a ' at the beginning of a text cell using Replace | Excel Discussion (Misc queries) | |||
Excel: replace all 2 spaces to FF/LR in column of text | Excel Discussion (Misc queries) | |||
Find and replace should work in Excel text boxes | Excel Discussion (Misc queries) | |||
How do I search and replace text in a textbox in Excel? | Excel Discussion (Misc queries) | |||
Excel - Find & Replace text in a string | Excel Worksheet Functions |