Microsoft Dynamics NAV News & Tips
Excel-lence: Dealing with Overlength Entries when Transferring Data between Systems

When you are transferring information to an accounting system, such as Dynamics NAV or any other database, you will typically have to adjust your existing data to fit within the rules and field sizes of the new, or target, system. Data conversions such as these are typically done via Microsoft Excel and it is not uncommon to be working with spreadsheets with thousands of rows. Identifying the offending records and fields can be burdensome, but with a simple Excel function, you can quickly accomplish this task.

How do you EASILY find the over-length strings? By using the LEN function, which returns the length of the data within a specified cell.

In this example we have a short list of names and addresses that need to be imported. The field for the street address has a maximum length of 20 characters.

click to enlarge

First, we need to insert a column for the LEN (sort for Length) function. It seems easiest to place it just to the right of the column we need to monitor.

click to enlarge

The LEN function will be entered into the first cell of our new column. The use of cell referencing, using the mouse to point at the cells required for our formula, will help us build the formula correctly.

• Type “=LEN(“ into the cell (without the “ marks).
• Then, without pressing another key, use the mouse to point to the cell you want to use in the formula and click on it.
• Finish the formula by entering the close parenthesis “)”.

In our example, the cell C1 now contains the formula =LEN(B1). Notice that when you finished entering the formula, the cell immediately displayed the results of the formula?

click to enlarge

Now copy the formula to the remaining cells of the column. A convenient way to do this is to select the cell where we just entered the formula (C1), and double click on the bottom right hand side of the cell border:

click to enlarge

This copies the cell formula into the cell below it - as long as the cell to the left, in this case column B, is not blank. If a cell is blank in the adjoining column, the replication will stop at that row. Alternately, copy the formula through all of the desired rows. The spreadsheet should now look similar to the following:

click to enlarge

Next, we will set a filter on the worksheet. This works best if you first insert a title row and populate some column headers.

click to enlarge

Then, we return to the first cell in our length column and activate the filter functionality.

click to enlarge

The filter activation button appears to the right of your header entries.

click to enlarge

Clicking it results in a drop down menu.

click to enlarge

By clicking on “Number Filters”, and using the criteria “Greater Than..”, you can now enter the field length you are limited to, in this case 20, which limits the list of records to street addresses that are Greater Than 20 characters.

click to enlarge

click to enlarge

Clicking the OK button activates the filter.

click to enlarge

Now that the screen is filtered, we can edit the entries as needed. Notice that as soon as we edited B2, its new (shorter) value is displayed in cell C2!

click to enlarge

Once all of the records conform to the new field length, you can turn off the filter by clicking on the filter activation button and then on the “Clear Filter…”

click to enlarge

To ensure the highest degree of accuracy, this process must be repeated for each field (column) in the spreadsheet.

Contact Mike Packard, Integrated Computer Systems Support, at 425-820-6120 for help or to learn more.

 

Data conversions between management or financial systems are typically done via Microsoft Excel and it is not uncommon to be working with spreadsheets with thousands of rows. How do you EASILY find the over-length strings? Fortunately, Excel provides an easy to use function that returns the length of the data.