Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 31st 09, 08:53 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 15
Default 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   Report Post  
Old August 31st 09, 08:59 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 2,819
Default 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   Report Post  
Old August 31st 09, 09:22 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 15
Default 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   Report Post  
Old August 31st 09, 09:24 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 22,908
Default 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   Report Post  
Old August 31st 09, 09:42 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 2,819
Default 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   Report Post  
Old August 31st 09, 10:35 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 15
Default 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   Report Post  
Old September 1st 09, 08:10 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 15
Default 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   Report Post  
Old September 1st 09, 08:26 PM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 2,819
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Insert a keyboard Character e.g (/)in multiple cells of a workshee Mike Excel Worksheet Functions 2 December 11th 08 03:36 PM
Inserting a hyphen after the 3rd character [email protected] Excel Worksheet Functions 3 October 29th 08 08:48 PM
inserting special character in a cell as a macro johnnyboy New Users to Excel 3 September 11th 06 09:05 PM
macro copy/paste data from multiple cells to multiple cells Diana Excel Discussion (Misc queries) 0 July 10th 06 09:24 PM
Add the same character(s) to multiple cells in a column (or row) . flashcatj Excel Discussion (Misc queries) 5 October 5th 05 04:37 PM


All times are GMT +1. The time now is 09:06 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017