Recently, I have had to import lots of different data for clients. They give me Excel worksheets that are full of names, numbers and telephone numbers. If you are working with Dynamics NAV or GP, you know that some of the fields can be very fussy in the import. Dynamics AX is more forgiving. I clean up the data, and get it ready.
Most clients ask me how I clean up columns, especially phone numbers. If you use Excel a lot, you will know these formulas or tricks. This post is for those who do not have to do this very often, and need some help.
1. Using Excel to change Words from All CAP to All lowercase, or to PROPER
- you can use the UPPER, LOWER, or PROPER functions to automatically change the case of existing text to uppercase, lowercase, or proper case.
- First, insert a temporary column next to the column that contains the text you want to convert
- In the temporary column, for example, cell B2, type =PROPER(A2)
- The other choices are =UPPER(A2) or =LOWER(A2)
Now, all of the names, countries, states, provinces, etc. can have standardized type.
2. Removing spaces and symbols in a phone number
- Often we get a list of telephone numbers that look like this
- To get rid of the (, ), –, Ext. 0000, use the find and replace function
- To get rid of the space, use the find and replace, using the space bar
- Press Ctrl + Space to select all cells in a column.
- Press Ctrl + H to open the “Find & Replace” dialog box.
- Press Space bar in the Find What field and make sure the “Replace with” field is empty.
3. Filling in the extension on a phone number
- Now you want to add trailing zeroes to make all the numbers with a fixed length, such as 14 digits for a telephone number in Canada and the US
- Create a blank column right of the existing phone number
- Use the EXCEL function =A2&REPT(“0”,14-LEN(A2)) where “A” represents the column with the telephone number in it
- The results are
Posted in Dynamics AX, Dynamics GP, Dynamics NAV, Excel, How To, Microsoft, Working
- Tagged Dynamics AX, Dynamics GP, Dynamics NAV, Excel, How To, Tips and Tricks
Graphic from news.techgenie.com
I love it when people take the time to write up step-by-step HOW TO articles or blogs. And, I like Excel HOW TO articles because it is an easy way to give a tutorial to a client when I need them to create/fix/organize data for loading into an ERP system.
Recently, I have found quite a few key Excel HOW TO procedures. As I like to keep things where I can easily find them, I am putting them on my blog so that others have the list, too.
Here we go.
1. Copy a formula in Excel http://www.wikihow.com/Copy-Formulas-in-Excel
2. Excel Data Validation for Dates (This is awesome!) http://www.contextures.com/exceldatavalidationdates.html#download
3. How to find duplicates in Excel and numbers (uses conditionals and COUNTIF) http://www.makeuseof.com/tag/find-duplicates-excel-numbers/
4. All about Pivot Tables http://www.excel-easy.com/data-analysis/pivot-tables.html or at https://fiveminutelessons.com/learn-microsoft-excel/how-create-pivot-table-excel
5. Eleven advanced Excel tricks – Vlookup, Index match, 3D Sum, $ sign, Ampersand symbol, arrays, goal seek, pivot tables, http://www.businessinsider.com/excel-tricks-vlookup-index-match-pivot-tables-array-2013-5#-1
6. How to split data from one column to two http://smallbusiness.chron.com/split-excel-data-two-columns-54661.html or https://www.ablebits.com/office-addins-blog/2014/02/27/split-cells-excel/
7. Link data in two workbooks https://www.youtube.com/watch?v=-foPogO3QNE
8. General Excel training from Microsoft https://support.office.com/en-us/article/Excel-training-9bc05390-e94c-46af-a5b3-d7c22f6990bb?ui=en-US&rs=en-US&ad=US
Dynamics CRM 2016 will be generally available in the fourth quarter of this year, delivering the “the most comprehensive upgrade ever” for the platform, Microsoft announced on Tuesday. See the announcement at http://news.microsoft.com/2015/09/08/microsoft-reinvents-productivity-with-upcoming-release-of-customer-engagement-solution/.
The new enhancements are designed to:
- increase productivity for organizations by providing a simple and seamless experience across their Dynamics CRM application
- easier integration with email (there will be a CRM app inside Outlook),
- easier integration with Excel and Dynamics CRM 2016 will offer an enhanced Excel experience including new Excel templates to automate tasks such as managing sales forecasts, for example.
- easier integration with OneDrive for Business for easier task management,
- an enhanced mobile experience for the worker on the go, and,
- enriched data and analytics,
Businesses will benefit from this new version of CRM.