Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
Display Modes | |
|
|