It's about time to learn macros in Microsoft Excel 2007 2010 2013 2016 365

Excel In Seconds Tips and Tricks from The Excel Addict - Microsoft Excel 2003, 2007, 2010, 2013, 2016, 365

June 14, 2016

Hi fellow Excel Addict,

Today's tip was suggested by one of my newsletter readers. If you are using a tip or trick in Excel that has been helpful to you, maybe it could help others as well. To share it with other Excel Addicts, just click reply on this email and tell me about it (some sample data would be nice as well). I'll try to include your tip in a future newsletter.

If you missed last Thursday's Excel in Minutes' tip, 'Easily Find Hundreds of Cool Symbols You Can Use in Excel', you can read it here now.

I hope you are having an awesome week! The weather here has been cold and wet for most of the last two weeks but the weatherman is promising more summer-like weather next week — and I'm sure he wouldn't lie ;-)

Keep on Excelling,
Francis Hayes (The Excel Addict)
Email: 
fhayes[AT]TheExcelAddict.com

Francis Hayes (TheExcelAddict.com)


TheExcelAddict.com Quote of the Day

"The art of being wise is the art of knowing what to overlook."

-- William James --



If you missed my last newsletter, you can click here to view it online.



Excel in Seconds with TheExcelAddict.com

Quickly Clean Up Inconsistent Phone Number Formats

Fix Inconsistent Phone Number Formats in Microsoft Excel 2007 2010 2013 2016 365Andrew told me about a problem he discovered in his office where several staff members were spending large amounts of time manually fixing inconsistent phone number formats in data they were getting from different sources.

Like many things in Excel, what's obvious to some Excel users may not be so obvious to others.

If you receive data that includes phone numbers with inconsistent formatting (e.g. 555-555-5555, (555)555-555, etc...), you need to know this simple tip that Andrew showed his coworkers that saved them many hours of manual cleanup every week.


1) Arrange all of the phone numbers in a column;

Format Numbers As Text in Microsoft Excel 2007 2010 2013 2016 3652) Since you won't likely be using phone numbers for doing calculations, I recommend changing the format of that column to Text. From the Number format dropdown on the Home tab, select Text;

3) With all of the cells containing the phone numbers selected, press CTRL+H to open the Find and Replace dialog;

4) In the 'Find what' field, enter the character (e.g. - ) that is separating the phone numbers;

Replace Phone Number Separators in Microsoft Excel 2007 2010 2013 2016 365

5) Leave the 'Replace with' field blank;

6) Click Replace All;

7) Repeat steps 3 to 6 if there are other characters separating the phone numbers.

Consistent Phone Number Formats in Microsoft Excel 2007 2010 2013 2016 365
Now that all of the phone numbers are consistent, if you want, you can apply a uniform format using the Special phone number format. Select More Number Formats... from the Number format dropdown on the Home tab, then click the Special Category and the Phone Number Type.

Special Phone Number Formats in Microsoft Excel 2007 2010 2013 2016 365
You can even create your own custom phone number format. After selecting the Phone Number format, click Custom in the Category list and modify the format code in the Type field.


It's about time to learn macros in Microsoft Excel 2007 2010 2013 2016 365
Thanks for supporting this newsletter and website



Earnings Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that for a few dollars. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are ever not completely satisfied with anything I recommend, please let me know and I will get your money back for you...GUARANTEED. You can't lose.
 'Spreadsheets Tips From An Excel Addict' and 'Excel in Seconds' are publications of TheExcelAddict.com.
Copyright Francis Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630