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!

Get in touch today to see how WDCi can help your business.