Excel Formulas for Salesforce Data Migration

Salesforce Data Migration Excel Formulas help you clean, transform, and validate CSV files before importing data into Salesforce. This cheat sheet includes commonly used Excel formulas for formatting dates, removing extra spaces, combining values, and preparing your data for successful Salesforce imports.

Salesforce Data Migrations require a significant amount of data preparation within Excel prior to file insertion. Here are a handful that speed up the data transformation and preparation process.

    • SSN formatting: =SUBSTITUTE(A1,”-“‘””)
    • SSN formatting: =TEXT(A1″000-00-0000″)
    • Date into text: =TEXT(A1 “mm/dd/yyyy”)
    • SSN no dashes, but leading zeros dropped: =REPT(0,9-LEN(A1))&A1
    • Date/Time: yyyy-mm-ddThh:mm:ss.sssZ
    • Vlookup number stored as text: Text to columns
    • Extra spaces: TRIM(A2)
    • Remove html/rtf: <*>
    • Multi-Select Picklist from rows: =IF(A2+A1,CONCATENATE(C1,”;”,B2),B2)
    • Date from datetime: =DATE(YEAR(B2), MONTH(B2), DAY(B2))
    • Remove the last character: =LEFT(A1, LEN(A1) -1)
    • Wrap ‘remove last character’ in TRIM: =TRIM(LEFT(A1, LEN(A1) -1))
    • Capitalize first letters: =Proper(A1)

Task

Remove extra spaces

Capitalize Names

Convert to Uppercase

Convert to Lowercase

Remove last character

Remove first character

Remove line breaks

Formula

=TRIM(A2)

=PROPER(A2)

=UPPER(A2)

=LOWER(A2)

=LEFT(A2,LEN(A2)-1)

=RIGHT(A2,LEN(A2)-1)

=SUBSTITUTE(A2,CHAR(10),” “)

Purpose

Cleans imported data

Standardizes names

Useful for IDs/codes

Email cleanup

Remove commas or semicolons

Remove unwanted prefixes

CSV cleanup

Task

Convert to Salesforce Date

Convert DateTime

Date only

MM/DD/YYYY

Formula

=TEXT(A2,”yyyy-mm-dd”)

=TEXT(A2,”yyyy-mm-ddThh:mm:ss.000Z”)

=DATE(YEAR(A2),MONTH(A2),DAY(A2))

=TEXT(A2,”mm/dd/yyyy”)

Salesforce imports dates in specific formats. These formulas help convert Excel dates into Salesforce-compatible values.

Especially useful because Salesforce IDs cause headaches.

Task

Add leading zeros

Convert number to text

Remove dashes

Formula

=REPT(“0”,9-LEN(A2))&A2

=TEXT(A2,”0″)

=SUBSTITUTE(A2,”-“,””)

These are probably the MOST used during migrations.

XLOOKUP (Excel 365)

=XLOOKUP(A2,OldIDs!A:A,OldIDs!B:B)

Find new Salesforce IDs from old system IDs.

VLOOKUP

=VLOOKUP(A2,$A$2:$B$500,2,FALSE)

Retrieve matching values.

INDEX MATCH

=INDEX(B:B,MATCH(A2,A:A,0))

More reliable than VLOOKUP.

Helpful formulas:

Duplicate?

=COUNTIF(A:A,A2)>1

Blank?

=ISBLANK(A2)

Length Check

=LEN(A2)

Required Field

=IF(A2=””,”Missing”,”OK”)