Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting same character into multiple cells--Macro?
I have an Excel 2003 worksheet that has 223 rows with dates listed as
2042009. I need this data to read 02042009. Is there any way to do this without having to manually enter the zero in 223 times. Would a macro help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting same character into multiple cells--Macro?
in B1 put ="0"&A1
Copy down 222 rows. then Select and Copy Paste Special, Values over the 223 rows in Column A marti wrote: I have an Excel 2003 worksheet that has 223 rows with dates listed as 2042009. I need this data to read 02042009. Is there any way to do this without having to manually enter the zero in 223 times. Would a macro help? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting same character into multiple cells--Macro?
Thanks, Bob That worked great!
I have one more question, similar in nature. I have a cell that reads like this: 01 02; "01 03"; "01 04" I have a large # of rows like this, as well. Is there any way to add in the parentheses around the first group of #s so that it will read: "01 02"; "01 03"; "01 04" "Bob I" wrote: in B1 put ="0"&A1 Copy down 222 rows. then Select and Copy Paste Special, Values over the 223 rows in Column A marti wrote: I have an Excel 2003 worksheet that has 223 rows with dates listed as 2042009. I need this data to read 02042009. Is there any way to do this without having to manually enter the zero in 223 times. Would a macro help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting same character into multiple cells--Macro?
A simple custom format of 00000000 will not do the job?
If not, in a helper column enter =0 & cellref Copy down. But why do you need the dates as 02042009 ? Gord Dibben MS Excel MVP On Mon, 31 Aug 2009 12:53:01 -0700, marti wrote: I have an Excel 2003 worksheet that has 223 rows with dates listed as 2042009. I need this data to read 02042009. Is there any way to do this without having to manually enter the zero in 223 times. Would a macro help? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting same character into multiple cells--Macro?
In B1 put =CHAR(34)&LEFT(A1,5)&CHAR(34)&MID(A1,6,18)
marti wrote: Thanks, Bob That worked great! I have one more question, similar in nature. I have a cell that reads like this: 01 02; "01 03"; "01 04" I have a large # of rows like this, as well. Is there any way to add in the parentheses around the first group of #s so that it will read: "01 02"; "01 03"; "01 04" "Bob I" wrote: in B1 put ="0"&A1 Copy down 222 rows. then Select and Copy Paste Special, Values over the 223 rows in Column A marti wrote: I have an Excel 2003 worksheet that has 223 rows with dates listed as 2042009. I need this data to read 02042009. Is there any way to do this without having to manually enter the zero in 223 times. Would a macro help? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting same character into multiple cells--Macro?
"Gord Dibben" wrote: A simple custom format of 00000000 will not do the job? If not, in a helper column enter =0 & cellref Copy down. But why do you need the dates as 02042009 ? Gord Dibben MS Excel MVP On Mon, 31 Aug 2009 12:53:01 -0700, marti wrote: I have an Excel 2003 worksheet that has 223 rows with dates listed as 2042009. I need this data to read 02042009. Is there any way to do this without having to manually enter the zero in 223 times. Would a macro help? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting same character into multiple cells--Macro?
Bob,
I would love to have an understanding of this, because it worked! Would you mind breaking down the different pieces of this for me to tell me what they are referring to? I would really appreciate it! "Bob I" wrote: In B1 put =CHAR(34)&LEFT(A1,5)&CHAR(34)&MID(A1,6,18) marti wrote: Thanks, Bob That worked great! I have one more question, similar in nature. I have a cell that reads like this: 01 02; "01 03"; "01 04" I have a large # of rows like this, as well. Is there any way to add in the parentheses around the first group of #s so that it will read: "01 02"; "01 03"; "01 04" "Bob I" wrote: in B1 put ="0"&A1 Copy down 222 rows. then Select and Copy Paste Special, Values over the 223 rows in Column A marti wrote: I have an Excel 2003 worksheet that has 223 rows with dates listed as 2042009. I need this data to read 02042009. Is there any way to do this without having to manually enter the zero in 223 times. Would a macro help? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting same character into multiple cells--Macro?
Char 34 gives you the Double quote
LEFT(A1,5) is the first five characters of your referenced cell A1 MID(A1,6,18) is the 18 characters of your referenced cell A1 starting with the sixth character. Stick them together with ampersand ( & ) to make a string of text. marti wrote: Bob, I would love to have an understanding of this, because it worked! Would you mind breaking down the different pieces of this for me to tell me what they are referring to? I would really appreciate it! "Bob I" wrote: In B1 put =CHAR(34)&LEFT(A1,5)&CHAR(34)&MID(A1,6,18) marti wrote: Thanks, Bob That worked great! I have one more question, similar in nature. I have a cell that reads like this: 01 02; "01 03"; "01 04" I have a large # of rows like this, as well. Is there any way to add in the parentheses around the first group of #s so that it will read: "01 02"; "01 03"; "01 04" "Bob I" wrote: in B1 put ="0"&A1 Copy down 222 rows. then Select and Copy Paste Special, Values over the 223 rows in Column A marti wrote: I have an Excel 2003 worksheet that has 223 rows with dates listed as 2042009. I need this data to read 02042009. Is there any way to do this without having to manually enter the zero in 223 times. Would a macro help? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add the same character(s) to multiple cells in a column (or row) . | Excel Discussion (Misc queries) | |||
Insert a keyboard Character e.g (/)in multiple cells of a workshee | Excel Worksheet Functions | |||
Inserting a hyphen after the 3rd character | Excel Worksheet Functions | |||
inserting special character in a cell as a macro | New Users to Excel | |||
macro copy/paste data from multiple cells to multiple cells | Excel Discussion (Misc queries) |