MrExcel.com - Your One Stop for Excel Tips & Solutions

Moving Subtotaled Data

October 21, 2001 - by

From this weekend's mailbag:

Carla writes:

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.

Troy asks:

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.