Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
C. Dales
 
Posts: n/a
Default Problems copying street addresses and dates

I have a number of cases where I need to copy the same information to several
cells either across a row or down a column.

The easiest way to do it seems to be to just grab the square in the lower
right corner of the source cell and drag it across as far as needed.

This works fine when copying a single cell and the source cell contents are
either pure digits as in a Zip code or pure text as in a person's name

However if the source cell is text containing numbers as in a street address
Excel increments the number part of the cell for each new cell that is
filled. The same thing happens if there is a date in the source cell. Excel
increments every cell by 1 day.

Even worse, the action for a cell with pure numbers changes if you combine
it with other cells containing text or mixed text/numbers. In that case, even
pure numbers get incremented.

As a result, copying something like the following ( ! indicates cell
divisions)
Joe Blow!123 Some St.!Some city!Some state!123456!10 Feb/05

gives the following result in the next row
Joe Blow!124 Some St.!Some city!Some state!123457!11 Feb/05

The name which in my case always changes gets copied as is while the
address, Zip code, and the date registered which are often the same for many
rows get changed by Excel.

How can I stop Excel from changing data I don't want changed during a copy?

I know that I can select the cells I want copied then select the target
cells and do a copy, however that is not as quick and I also find myself
getting in trouble because Excel changes what happens when drag copying
depending on the cell contents. Since plain text or plain numbers can easily
be drag copied, it is very easy to forget about the cell contents and drag
copy a mixed number/text cell and that results in errors.


  #2   Report Post  
Martin P
 
Posts: n/a
Default

I think this will be solved if you copy and use Edit, Paste Special, Values
in the target cells.

"C. Dales" wrote:

I have a number of cases where I need to copy the same information to several
cells either across a row or down a column.

The easiest way to do it seems to be to just grab the square in the lower
right corner of the source cell and drag it across as far as needed.

This works fine when copying a single cell and the source cell contents are
either pure digits as in a Zip code or pure text as in a person's name

However if the source cell is text containing numbers as in a street address
Excel increments the number part of the cell for each new cell that is
filled. The same thing happens if there is a date in the source cell. Excel
increments every cell by 1 day.

Even worse, the action for a cell with pure numbers changes if you combine
it with other cells containing text or mixed text/numbers. In that case, even
pure numbers get incremented.

As a result, copying something like the following ( ! indicates cell
divisions)
Joe Blow!123 Some St.!Some city!Some state!123456!10 Feb/05

gives the following result in the next row
Joe Blow!124 Some St.!Some city!Some state!123457!11 Feb/05

The name which in my case always changes gets copied as is while the
address, Zip code, and the date registered which are often the same for many
rows get changed by Excel.

How can I stop Excel from changing data I don't want changed during a copy?

I know that I can select the cells I want copied then select the target
cells and do a copy, however that is not as quick and I also find myself
getting in trouble because Excel changes what happens when drag copying
depending on the cell contents. Since plain text or plain numbers can easily
be drag copied, it is very easy to forget about the cell contents and drag
copy a mixed number/text cell and that results in errors.


  #3   Report Post  
Ed Lawrence
 
Posts: n/a
Default

Hi,
When you drag a cell there is a little box that appears. This is the
autofill options box. open the box and select copy cells

"C. Dales" wrote:

I have a number of cases where I need to copy the same information to several
cells either across a row or down a column.

The easiest way to do it seems to be to just grab the square in the lower
right corner of the source cell and drag it across as far as needed.

This works fine when copying a single cell and the source cell contents are
either pure digits as in a Zip code or pure text as in a person's name

However if the source cell is text containing numbers as in a street address
Excel increments the number part of the cell for each new cell that is
filled. The same thing happens if there is a date in the source cell. Excel
increments every cell by 1 day.

Even worse, the action for a cell with pure numbers changes if you combine
it with other cells containing text or mixed text/numbers. In that case, even
pure numbers get incremented.

As a result, copying something like the following ( ! indicates cell
divisions)
Joe Blow!123 Some St.!Some city!Some state!123456!10 Feb/05

gives the following result in the next row
Joe Blow!124 Some St.!Some city!Some state!123457!11 Feb/05

The name which in my case always changes gets copied as is while the
address, Zip code, and the date registered which are often the same for many
rows get changed by Excel.

How can I stop Excel from changing data I don't want changed during a copy?

I know that I can select the cells I want copied then select the target
cells and do a copy, however that is not as quick and I also find myself
getting in trouble because Excel changes what happens when drag copying
depending on the cell contents. Since plain text or plain numbers can easily
be drag copied, it is very easy to forget about the cell contents and drag
copy a mixed number/text cell and that results in errors.


  #4   Report Post  
Gord Dibben
 
Posts: n/a
Default

Select the range of cells to copy.

Right-click and drag down.

Release button and select "Copy Cells" from the options presented.

No incrementation will take place.


Gord Dibben Excel MVP

On Thu, 10 Feb 2005 08:57:01 -0800, "C. Dales" <C.
wrote:

I have a number of cases where I need to copy the same information to several
cells either across a row or down a column.

The easiest way to do it seems to be to just grab the square in the lower
right corner of the source cell and drag it across as far as needed.

This works fine when copying a single cell and the source cell contents are
either pure digits as in a Zip code or pure text as in a person's name

However if the source cell is text containing numbers as in a street address
Excel increments the number part of the cell for each new cell that is
filled. The same thing happens if there is a date in the source cell. Excel
increments every cell by 1 day.

Even worse, the action for a cell with pure numbers changes if you combine
it with other cells containing text or mixed text/numbers. In that case, even
pure numbers get incremented.

As a result, copying something like the following ( ! indicates cell
divisions)
Joe Blow!123 Some St.!Some city!Some state!123456!10 Feb/05

gives the following result in the next row
Joe Blow!124 Some St.!Some city!Some state!123457!11 Feb/05

The name which in my case always changes gets copied as is while the
address, Zip code, and the date registered which are often the same for many
rows get changed by Excel.

How can I stop Excel from changing data I don't want changed during a copy?

I know that I can select the cells I want copied then select the target
cells and do a copy, however that is not as quick and I also find myself
getting in trouble because Excel changes what happens when drag copying
depending on the cell contents. Since plain text or plain numbers can easily
be drag copied, it is very easy to forget about the cell contents and drag
copy a mixed number/text cell and that results in errors.


  #5   Report Post  
SauveC
 
Posts: n/a
Default

If you hold the CTRL key down when you are doing autofil you will get the
opposite results to what Excel would normally do. For example, if you have
123 Your Street in a cell and you drag the autofil button down, Excel will
automaticall increment the 123 to 124 and then 125 etc. But if you hold the
CTRL key down while doing this Excel will not increment the number value.
Try it.

"C. Dales" wrote:

I have a number of cases where I need to copy the same information to several
cells either across a row or down a column.

The easiest way to do it seems to be to just grab the square in the lower
right corner of the source cell and drag it across as far as needed.

This works fine when copying a single cell and the source cell contents are
either pure digits as in a Zip code or pure text as in a person's name

However if the source cell is text containing numbers as in a street address
Excel increments the number part of the cell for each new cell that is
filled. The same thing happens if there is a date in the source cell. Excel
increments every cell by 1 day.

Even worse, the action for a cell with pure numbers changes if you combine
it with other cells containing text or mixed text/numbers. In that case, even
pure numbers get incremented.

As a result, copying something like the following ( ! indicates cell
divisions)
Joe Blow!123 Some St.!Some city!Some state!123456!10 Feb/05

gives the following result in the next row
Joe Blow!124 Some St.!Some city!Some state!123457!11 Feb/05

The name which in my case always changes gets copied as is while the
address, Zip code, and the date registered which are often the same for many
rows get changed by Excel.

How can I stop Excel from changing data I don't want changed during a copy?

I know that I can select the cells I want copied then select the target
cells and do a copy, however that is not as quick and I also find myself
getting in trouble because Excel changes what happens when drag copying
depending on the cell contents. Since plain text or plain numbers can easily
be drag copied, it is very easy to forget about the cell contents and drag
copy a mixed number/text cell and that results in errors.


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



All times are GMT +1. The time now is 06:29 AM.

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

About Us

"It's about Microsoft Excel"