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?

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?

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?

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?

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?

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?

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?

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?