Parsing Data from One Column to Multiple Columns - MapInfo_Pro - 2023

If you have imported data into MapInfo Pro from another format, there is always the chance that the data will not come in exactly the way you want it. In some instances, items that should appear in separate columns may be put together in one column. The following is a series of column updates that parses one column of full names (first, middle, and last) into three parts. It works even if there is no middle name, or if there is only a last name.

To parse data from one column to multiple columns:

1. On the HOME tab, click Open, select Table and open the table to be modified.
2. Add three new columns to your table. On the TABLE tab, in the Maintenance group, click Table, and Modify Structure. Add two character columns of size 15. Call them FIRST and MIDDLE. Then add a character column called LAST of size 30.
3. Put the full name into the column called LAST by choosing the TABLE tab, and clicking Update Column. Fill in the Update Column dialog box.
   
   

   

   
   

   The column to update is LAST, and we get the Value from the column that has the full name in it. Remember we are only working with one table, so the Table to Update and Get Value From Table should be the same table. In the following example, the table is TABLE1 and the full name column is your_full_name_column. You should fill in your own values for these two items.

4. To parse the first name out of the full name column, choose the TABLE tab and click Update Column. Fill in the Update Column dialog box.
   
   

   

   
   

   The Column to Update is: FIRST

   The Value is: left$(LAST, instr(1,LAST," "))

5. To parse the last name out of the full name column, on the TABLE tab, click Update Column. Fill in the Update Column dialog box.
   
   

   

   
   

   The Column to update is: LAST

   The Value is: Right$(LAST, Len(LAST)-Instr(1,LAST," ")).

6. To parse the middle name out of the full name column, on the TABLE tab, click Update Column. Fill in the Update Column dialog box.
   
   

   

   
   

   Update the MIDDLE column with the Value: Left$(LAST, Instr(1,LAST," "))

7. Then update the LAST column again by returning to the TABLE tab, clicking Update Column, and filling in the dialog box.
   
   

   

   
   

   The Value is: Right$(LAST, Len(LAST)-Instr(1,LAST," "))

8. Click OK to update the column.