Moving Subtotaled Data
October 21, 2001 - by Bill Jelen
From this weekend's mailbag:
How do you copy & paste a subtotaled table onto a worksheet?
Let's say you have a thousand-line table. With clever subtotaling, you create a 30-line summary. You want to copy this summary onto a worksheet containing other information. It's a one-page summary report with data from all over heck n gone.
With ordinary copy-and-paste, the whole entire damn table gets copied. I've been re-tying the information most laboriously. There must be a shortcut!
Yes, Carla there is. It involves the use of the Goto-Special dialog and then selecting Visible Cells Only.
Is there a way to convert a database of phone numbers that look like this 123/456-7890 or 123-456-7890 to (123) 456-7890?
Yes - Assuming your phone numbers are in A2:A4001, enter this formula in a blank B2:
="(" & LEFT(A2, 3) & ") " & MID(A2, 5, 8)
Then copy down to B2:B4001.
Before deleting column A, you will want to change the formulas in column B to values. Highlight B2:B4001. Use Ctrl + C to Copy, then Edit - PasteSpecial - Values - OK to change the formulas to values.