Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

excel advanced - excel

Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel advanced - Excel,

excel advanced - Excel,

ResolvedVersion Standard

Dianne has attended:
Excel Intermediate course

Excel,

We have a huge list of telephone numbers, is there an easy way in which we could delete all the gaps within these numbers other than one by one using F2. please please the list is HUGE

RE: Excel,

Hi Dianne

Thank you for your question. I understand how this could be a frustrating and time consuming process if you were to do it manually.

Can you please tell me if all the phone numbers have the same number of digits within them?

thanks
Amanda

RE: Excel,

No, as they are telephone numbers the amounts of digits will be different.

Thanks
Dianne

RE: Excel,

Hi Dianne

Using a simple find and replace should hopefully resolve this.

1. Go to Edit - Replace (or Ctrl + H)
2. Click the the Find what box, press the spacebar once (meaning find a gap of one space) - you don't need to type anything into the Replace with box.
3. Click Replace All. Excel will search for all instances of one space and replace it with no space, eliminating any spaces.

I hope this helps.
Amanda

Excel tip:

Quickly copy a formula across sheets

Suppose you have a formula in cell Sheet1!B2, say =A1*5%, that you wish to copy to cell B2 on Sheet2, Sheet3 and Sheet4. Instead of using copy and paste, try this: (1) Select Sheet1!B2. (2) Group Sheet1 with the worksheets Sheet2, Sheet3 and Sheet4 by holding down Ctrl and clicking on the tabs of the sheets to group them. (3) Press the F2 key, then immediately press Enter to copy the formula in Sheet1!B2 across the grouped sheets.

Remember to ungroup the sheets afterwards! Right-click on any tab and choose Ungroup Sheets to do that.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.