Data Migration: Tips to clean up excel data
When we implement a new integration project, we often need to load historical data into a new system. For example, the customer may provide us with a CSV export and expect us to load the data into Salesforce.
Below are some Excel formula tips you may find them handy while cleaning up the data:
Determine if record is a Person or Company
Some system tends to have the same column to keep track of Person FullName and Company Name. If you have a same set of data as below, you can split them using certain rules.
For example:
| A | |
| 1 | Customer Name |
| 2 | Lawrence, Alex |
| 3 | Smith, Peter Michael |
| 4 | WDCI |
* We assume that if the record consists of a comma (,) , it will be treated as a Person record
By using the FIND function, you can determine the position of the comma (,). For example: =FIND(“,”,A2)
| A | B | |
| 1 | Customer Name | Individual? |
| 2 | Lawrence, Alex | 9 |
| 3 | Smith, Peter Michael | 6 |
| 4 | WDCI | #VALUE! |
Once you get the result above, you differentiate them by the value in column B. If a cell in column B consists integer, it is a Person record. If not, it will be treated as Company record.
Split the customer name to First Name & Last Name
Once you have done the separation between Person record and Company record, you may want to split them by First Name and Last Name
| A | |
| 1 | Customer Name |
| 2 | Lawrence, Alex |
| 3 | Smith, Peter |
| 4 | Shearer, Alan |
* We assume that the format is <LastName>, <FirstName>
To get the Last Name, you can use =LEFT(A2,FIND(“,”,A2)-1) in column B
| A | B | |
| 1 | Customer Name | Last Name |
| 2 | Lawrence, Alex | Lawrence |
| 3 | Smith, Peter | Smith |
| 4 | Shearer, Alan | Shearer |
To get the First Name, you can use =RIGHT(A2,LEN(A2)-FIND(“,”,A2)) in column
| A | B | |
| 1 | Customer Name | First Name |
| 2 | Lawrence, Alex | Alex |
| 3 | Smith, Peter | Peter |
| 4 | Shearer, Alan | Alan |
Each migration scenario differs from another. The methods above may only apply to the specific scenario mentioned above. Hope it helps!
