Cell formated as text changes when copied

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jjk98 - 22 May 2007 00:03 GMT

Spreadsheet with 10 rows (all formated as text) and 1 column. I enter the
string: Usages in 1900 - 1905 in the top row. I select the top row and catch
the sqaure at the lower right corner and "copy" the text to the next 9 rows.

The text in the second line is now "Usages in 1900 - 1901"
in the third row it is "Usages in 1900 - 1902"
and so on.

Why is the text changing?

Signature

Thanks for your help - jjk98

Reply to this Message

jjk98 - 22 May 2007 00:10 GMT

Oops!

I typed the description incorrectly - the first line of text is "Usages in
1900 - 1900"

Also if the year is not the last thing on the line then it works fine. So if
I type the first line as "Usages in 1900 - 1900 summarized" then the second
year does not change.

Signature

Thanks for your help - jjk98

> Spreadsheet with 10 rows (all formated as text) and 1 column. I enter the
> string: Usages in 1900 - 1905 in the top row. I select the top row and catch
[quoted text clipped - 5 lines]
>
> Why is the text changing?

Reply to this Message

Jim Skrydlak - 22 May 2007 00:44 GMT

This is, I think, one of those situations in which Excel is trying to help,
but is perhaps a bit smarter than you'd like it to be. If you copy to the
next nine rows by executing an Edit Copy followed by selecting the cells and
doing a paste, it won't do that (I think). Same if you do it by
right-clicking on the first cell, selecting copy, then selecting the next
nine cells and doing a paste. Dragging that handle at the lower right-hand
corner frequently increments things by one.

Jim

> Oops!
>
[quoted text clipped - 14 lines]
> >
> > Why is the text changing?

Reply to this Message

jjk98 - 22 May 2007 00:54 GMT

Thank you, both solutions work as you describe. Would you consider the action
I found a "bug"?

Signature

Thanks for your help - jjk98

Reply to this Message

Gord Dibben - 22 May 2007 03:45 GMT

That is Excel being helpful and autofilling for you.

Although row 2 should read Usages in 1900 - 1906

i.e. only the 1905 will increment.

Right-click on the fill handle(little square) and drag down.

Release and "Copy Cells"

Gord Dibben MS Excel MVP

>Spreadsheet with 10 rows (all formated as text) and 1 column. I enter the
>string: Usages in 1900 - 1905 in the top row. I select the top row and catch
[quoted text clipped - 5 lines]
>
>Why is the text changing?

Reply to this Message

jjk98 - 22 May 2007 13:15 GMT

Thanks to both suggestions, both work. And you are correct, it is Excel
"helping" even though I tried to disable the "help" by making it a text field.

Sort of like the most dreaded words in the US: "We're from the government
and we're here to help!"

Signature

Thanks for your help - jjk98

Reply to this Message