Excel Formulas for Transforming Data

Exporting & Importing Data

Exporting and Importing Data Export Reports Exporting Data using any Report Exporting Contacts using Contacts and Sales Pipeline Dashboards Importing Contacts using CSV Importing Event Attendees using CSV Excel Formulas for Transforming Data

Excel Formulas for Transforming Data

Below is a list of Microsoft Excel formulas that you can use to manipulate the data in Excel before uploading it to LeadCenter. These formulas will help you update thousands of columns in Excel before importing them to LeadCenter.

1. Phone number: clean “-“, “ “, “(“, and “)” and add 1 at the beginning

=CONCAT("1",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(L2,"-","")," ",""),"(",""),")",""))

2. Phone number clean up and add 1 at the beginning (More accurate formula)

=IF(AI2="","",IF(LEFT(AI12)="+",AI2,CONCAT("1",LET(chars,MID(AI2,SEQUENCE(LEN(AI2)),1),TEXTJOIN("",1,FILTER(chars,ISNUMBER(chars+0)))+0))))

3. Get the first name from the full name

=LEFT(C2,(FIND(" ",C2,1)-1))

4. Get the last name from the full name

=MID(C2,FIND(" ",C2)+1,LEN(C2))

5. Extract email from string

=IF(TRIM(RIGHT(SUBSTITUTE(LEFT(G5,FIND(" ",G5&" ",FIND("@",G5))-1)," ",REPT(" ",LEN(G5))),LEN(G5)))<>"",TRIM(RIGHT(SUBSTITUTE(LEFT(G5,FIND(" ",G5&" ",FIND("@",G5))-1)," ",REPT(" ",LEN(G5))),LEN(G5))),"")

6. Split first name and last name

First name from cell A2:

=LEFT(A2,FIND("",A2&"")-1)

Last name from cell A2:

=RIGHT(A2,LEN(A2)-FIND("",A2&""))

7. Change values in a cell based on existing values

The formula below will change the values in the G2 cell based on the following:

CA -> LG4-California
NM -> LG4-New Mexico
AZ -> LG4-Arizona

=IF(G2="CA","LG4-California",IF(G2="NM","LG4-New Mexico",IF(G2="AZ","LG4-Arizona")))

Need more help?

If you can’t find the answers you’re looking for, our support specialists are available to answer your questions and troubleshoot if necessary.

Phone Call (888) 291-7116. Our main hours are Monday to Friday 7 am-5 pm Central Time.
Support Ticket Send your questions and inquiries via email to support@leadcenter.ai. A support ticket will be created and one of our team members will get back to you as quickly as possible.