 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2008Arithmetic on text cells?
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) >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". [quoted text clipped - 5 lines] > 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. [quoted text clipped - 9 lines] >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... 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 >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". [quoted text clipped - 5 lines] > 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 >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". [quoted text clipped - 5 lines] >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" Is that different from Ctrl-drag? It you simply try Right-click and drag, you'll see the difference. Tyro >>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. 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. >>>Right-click and drag then release button and select "Copy Cells" >> >> Is that different from Ctrl-drag? And your point is? Tyro >>It you simply try Right-click and drag, you'll see the difference. > [quoted text clipped - 17 lines] >>> >>> Is that different from Ctrl-drag? >And your point is? Over your head, apparently. >>>It you simply try Right-click and drag, you'll see the difference. >> [quoted text clipped - 17 lines] >>>> >>>> Is that different from Ctrl-drag? You're describing standard Excel operations. Tyro >>And your point is? > [quoted text clipped - 21 lines] >>>>> >>>>> Is that different from Ctrl-drag? Depends upon how your original data is entered. abc1 right-click and drag allows for "copy cell" and "fill series" 1abc right-click and drag gives you "copy cells" only. Obviously Excel sees no "series" to fill in the second case. Any rules to it? None that I know of. Your post before this one with the various examples seems to have no system I can point to. Gord >>It you simply try Right-click and drag, you'll see the difference. > [quoted text clipped - 17 lines] >>> >>> Is that different from Ctrl-drag? >Depends upon how your original data is entered. > [quoted text clipped - 8 lines] >Your post before this one with the various examples seems to have no system I >can point to. Overall, it's a usable system. If I want to suppress the fill, I just use the Ctrl key. Even if I forget, I can "redo" it using the fill options pop-up, at least in 2007. Now that I understand it... ;-) Sometimes helpful software is anything but. But if they give me an option to change it, it's cool. > >Depends upon how your original data is entered. > [quoted text clipped - 17 lines] > Sometimes helpful software is anything but. But if they give me an > option to change it, it's cool. I think this will also help u To Fill right side use Ctrl + R To fill downwards use Ctrl + D
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.