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

For example:
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!