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:
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.
|3||Smith, Peter Michael|
* 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)
|3||Smith, Peter Michael||6|
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.
Once you have done the separation between Person record and Company record, you may want to split them by First Name and Last Name
* We assume that the format is <LastName>, <FirstName>
To get the Last Name, you can use =LEFT(A2,FIND(“,”,A2)-1) in column B
|1||Customer Name||Last Name|
To get the First Name, you can use =RIGHT(A2,LEN(A2)-FIND(“,”,A2)) in column
|1||Customer Name||First Name|
Each migration scenario differs from another. The methods above may only apply to the specific scenario mentioned above. Hope it helps!