I created a column with text data. I explicitly set the cell format to
Text. The values in these cells are "Month", "90 Days", and "Year".

I entered "Month" in the first cell and copied it down 7-8 cells by
dragging the corner. Great. All cells show "Month".

Then I engter "90 Days" in the next cell down and copy that down
another 7-8 cells. Surprise. Those cells show "91 Days", "92 Days",
"93 Days", etc. Why is Excel doing arithmetic on text cells?

Excel isn't doing text arithmetic....it's *helping*.
It thinks you want to increment a text series.

Example:
A1: Employee1
Drag down using the fill handle
and you get:
A2: Employee2
A3: Employee3

To disable the feature for only the current instance....
While dragging to fill the range:
Hold down the CTRL key.

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Three Lefts" <spamtrap@spamtrap.invalid> wrote in message
news:3doau3p45t4lle8t8nvj6ohutrrutiu47a@4ax.com...
>I created a column with text data. I explicitly set the cell format to
> Text. The values in these cells are "Month", "90 Days", and "Year".
>
> I entered "Month" in the first cell and copied it down 7-8 cells by
> dragging the corner. Great. All cells show "Month".
>
> Then I engter "90 Days" in the next cell down and copy that down
> another 7-8 cells. Surprise. Those cells show "91 Days", "92 Days",
> "93 Days", etc. Why is Excel doing arithmetic on text cells?

It's because Excel is trying to help you. If you don't want the number 90 to
increment, just hold the Ctrl key as you drag. Excel does this as many times
you want text such as Qtr 1 to become Qtr 2, Qtr 3 etc as you drag. It is no
surprise. It's the way Excel works. . BTW if you enter a date such as
3/22/2008 and drag it, it will increment too unless you hold Ctrl while you
drag. Also if you enter a number such as 1 and hold Ctrl, it will increment
too. It's an Excel feature, designed to help you.

Tyro

"Three Lefts" <spamtrap@spamtrap.invalid> wrote in message
news:3doau3p45t4lle8t8nvj6ohutrrutiu47a@4ax.com...
>I created a column with text data. I explicitly set the cell format to
> Text. The values in these cells are "Month", "90 Days", and "Year".
>
> I entered "Month" in the first cell and copied it down 7-8 cells by
> dragging the corner. Great. All cells show "Month".
>
> Then I engter "90 Days" in the next cell down and copy that down
> another 7-8 cells. Surprise. Those cells show "91 Days", "92 Days",
> "93 Days", etc. Why is Excel doing arithmetic on text cells?

Right-click and drag then release button and select "Copy Cells"


Gord Dibben MS Excel MVP

On Sat, 22 Mar 2008 12:48:30 -0700, Three Lefts <spamtrap@spamtrap.invalid>
wrote:

>I created a column with text data. I explicitly set the cell format to
>Text. The values in these cells are "Month", "90 Days", and "Year".
>
>I entered "Month" in the first cell and copied it down 7-8 cells by
>dragging the corner. Great. All cells show "Month".
>
>Then I engter "90 Days" in the next cell down and copy that down
>another 7-8 cells. Surprise. Those cells show "91 Days", "92 Days",
>"93 Days", etc. Why is Excel doing arithmetic on text cells?

On Sat, 22 Mar 2008 16:00:34 -0400, "Ron Coderre"
<ronREMOVETHIScoderre@bigfoot.com> wrote:

>Excel isn't doing text arithmetic....it's *helping*.
>It thinks you want to increment a text series.
>
>Example:
>A1: Employee1
>Drag down using the fill handle
>and you get:
>A2: Employee2
>A3: Employee3
>
>To disable the feature for only the current instance....
>While dragging to fill the range:
>Hold down the CTRL key.

Is there a name for this feature?

I did a little testing:

If I start with "1 2 3", I get:

1 2 3
2 2 3
3 2 3
4 2 3

If I start with "Phase 1, Part 1", I get:

Phase 1, Part 1
Phase 1, Part 2
Phase 1, Part 3
Phase 1, Part 4

But if I start with "Part 1 of 10", I get:

Part 1 of 10
Part 1 of 11
Part 1 of 12
Part 1 of 13
Part 1 of 14

Curious...

On Sat, 22 Mar 2008 13:54:11 -0700, Gord Dibben <gorddibbATshawDOTca>
wrote:

>Right-click and drag then release button and select "Copy Cells"

Is that different from Ctrl-drag?

It you simply try Right-click and drag, you'll see the difference.

Tyro

"Three Lefts" <spamtrap@spamtrap.invalid> wrote in message
news:m7tau31o2ss6hbipug1mp3anl59h5mc1u5@4ax.com...
> On Sat, 22 Mar 2008 13:54:11 -0700, Gord Dibben <gorddibbATshawDOTca>
> wrote:
>
>>Right-click and drag then release button and select "Copy Cells"
>
> Is that different from Ctrl-drag?

On Sat, 22 Mar 2008 14:15:22 -0700, "Tyro" <Tyro@hotmail.com> wrote:

>It you simply try Right-click and drag, you'll see the difference.

I did try it. It looked like Ctrl+Drag was the same as Right-Drag with
Copy Cells. Both suppressed the "Fill Series" function (I guess that's
what it's called?).

Interestingly, it makes a difference when the Ctrl key is released. It
does NOT make a difference when it is depressed so long as it is NOT
before the drag button is released.

That is, I can do the drag and then just before releasing the drag
button, hold the Ctrl key to suppress the fill. This is a lot easier
than holding the Ctrl key during the entire drag operation.

Also, no matter how I do the copy, at least for Excel 2007, I get the
little Auto Fill Options pop-up which will let me change the action
after the fact.


>
>"Three Lefts" <spamtrap@spamtrap.invalid> wrote in message
>news:m7tau31o2ss6hbipug1mp3anl59h5mc1u5@4ax.com.. .
>> On Sat, 22 Mar 2008 13:54:11 -0700, Gord Dibben <gorddibbATshawDOTca>
>> wrote:
>>
>>>Right-click and drag then release button and select "Copy Cells"
>>
>> Is that different from Ctrl-drag?
>

And your point is?

Tyro

"Three Lefts" <spamtrap@spamtrap.invalid> wrote in message
news:28uau3913ouj30nk85kks8i6365em5f302@4ax.com...
> On Sat, 22 Mar 2008 14:15:22 -0700, "Tyro" <Tyro@hotmail.com> wrote:
>
>>It you simply try Right-click and drag, you'll see the difference.
>
> I did try it. It looked like Ctrl+Drag was the same as Right-Drag with
> Copy Cells. Both suppressed the "Fill Series" function (I guess that's
> what it's called?).
>
> Interestingly, it makes a difference when the Ctrl key is released. It
> does NOT make a difference when it is depressed so long as it is NOT
> before the drag button is released.
>
> That is, I can do the drag and then just before releasing the drag
> button, hold the Ctrl key to suppress the fill. This is a lot easier
> than holding the Ctrl key during the entire drag operation.
>
> Also, no matter how I do the copy, at least for Excel 2007, I get the
> little Auto Fill Options pop-up which will let me change the action
> after the fact.
>
>
>>
>>"Three Lefts" <spamtrap@spamtrap.invalid> wrote in message
>>news:m7tau31o2ss6hbipug1mp3anl59h5mc1u5@4ax.com. ..
>>> On Sat, 22 Mar 2008 13:54:11 -0700, Gord Dibben <gorddibbATshawDOTca>
>>> wrote:
>>>
>>>>Right-click and drag then release button and select "Copy Cells"
>>>
>>> Is that different from Ctrl-drag?
>>

On Sat, 22 Mar 2008 14:43:09 -0700, "Tyro" <Tyro@hotmail.com> wrote:

>And your point is?

Over your head, apparently.

>"Three Lefts" <spamtrap@spamtrap.invalid> wrote in message
>news:28uau3913ouj30nk85kks8i6365em5f302@4ax.com.. .
>> On Sat, 22 Mar 2008 14:15:22 -0700, "Tyro" <Tyro@hotmail.com> wrote:
>>
>>>It you simply try Right-click and drag, you'll see the difference.
>>
>> I did try it. It looked like Ctrl+Drag was the same as Right-Drag with
>> Copy Cells. Both suppressed the "Fill Series" function (I guess that's
>> what it's called?).
>>
>> Interestingly, it makes a difference when the Ctrl key is released. It
>> does NOT make a difference when it is depressed so long as it is NOT
>> before the drag button is released.
>>
>> That is, I can do the drag and then just before releasing the drag
>> button, hold the Ctrl key to suppress the fill. This is a lot easier
>> than holding the Ctrl key during the entire drag operation.
>>
>> Also, no matter how I do the copy, at least for Excel 2007, I get the
>> little Auto Fill Options pop-up which will let me change the action
>> after the fact.
>>
>>
>>>
>>>"Three Lefts" <spamtrap@spamtrap.invalid> wrote in message
>>>news:m7tau31o2ss6hbipug1mp3anl59h5mc1u5@4ax.com ...
>>>> On Sat, 22 Mar 2008 13:54:11 -0700, Gord Dibben <gorddibbATshawDOTca>
>>>> wrote:
>>>>
>>>>>Right-click and drag then release button and select "Copy Cells"
>>>>
>>>> Is that different from Ctrl-drag?
>>>
>