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)
If you missed my last newsletter, you can click here to view it online.
Quickly Clean Up Inconsistent Phone Number Formats
Andrew 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;
2) 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;
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.
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.
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.
| 'Spreadsheets Tips
Excel Addict' and 'Excel in Seconds' are publications of
Copyright Francis Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630