How To Prepare Your CSV Import Using Excel
How to get telephone number, first name, and last name in the correct format
Telephone number starts with 0
A |
B |
C |
D |
First Name |
Last Name |
Phone Number ❌ |
Phone Number ✅ |
Maurizio |
Superchat |
0156784321001 |
+49156784321001 |
Oliver |
Superchat |
0156784321002 |
|
Inka |
Superchat |
0156784321003 |
|
Assuming your Excel file is set up as above:
C2 = Column with the number starting with 0
D2 = Empty column for the correctly formatted number
To format the number, follow these steps:
-
Insert the following formula into D2:
=SUBSTITUTE(C2, "0", "+49", 1)
-
Now the telephone number from C2 should appear in the correct format in D2.
Telephone number starts with 0049
A |
B |
C |
D |
First Name |
Last Name |
Phone Number ❌ |
Phone Number ✅ |
Maurizio |
Superchat |
0049156784321001 |
+49156784321001 |
Oliver |
Superchat |
0049156784321002 |
|
Inka |
Superchat |
0049156784321003 |
|
Assuming your Excel file is set up as above:
C2 = Column with the number starting with 0049
D2 = Empty column for the correctly formatted number
To format the number, follow these steps:
-
Insert the following formula into D2:
=SUBSTITUTE(C2, "0049", "+49", 1)
-
Now the telephone number from C2 should appear in the correct format in D2.
Telephone number starts with 1
A |
B |
C |
D |
First Name |
Last Name |
Phone Number ❌ |
Phone Number ✅ |
Maurizio |
Superchat |
156784321001 |
+49156784321001 |
Oliver |
Superchat |
156784321002 |
|
Inka |
Superchat |
156784321003 |
|
Assuming your Excel file is set up as above:
C2 = Column with the number starting with 1
D2 = Empty column for the correctly formatted number
To format the number, follow these steps:
-
Insert the following formula into D2:
=LINK("+49", C2)
-
Now the telephone number from C2 should appear in the correct format in D2.
Telephone number starts with 49
A |
B |
C |
D |
First Name |
Last Name |
Phone Number ❌ |
Phone Number ✅ |
Maurizio |
Superchat |
49156784321001 |
+49156784321001 |
Oliver |
Superchat |
49156784321002 |
|
Inka |
Superchat |
49156784321003 |
|
Assuming your Excel file is set up as above:
C2 = Column with the number starting with 49
D2 = Empty column for the correctly formatted number
To format the number, follow these steps:
-
Insert the following formula into D2:
=LINK("+", C2)
-
Now the telephone number from C2 should appear in the correct format in D2.
Telephone number contains characters
A |
B |
C |
D |
First Name |
Last Name |
Phone Number ❌ |
Phone Number ✅ |
Maurizio |
Superchat |
+49 15678 4321001 |
+49156784321001 |
Oliver |
Superchat |
+4915678/4321002 |
+49156784321002 |
Inka |
Superchat |
+49AA156784321003 |
+49156784321003 |
You don't need to do anything further here; all characters will be ignored during upload. The important thing is that the number starts with +49.
Separating First Name & Last Name
A |
B |
C |
Name ❌ |
First Name ✅ |
Last Name ✅ |
Maurizio Blötscher |
Maurizio |
Blötscher |
Oliver Klötzer |
Oliver |
Klötzer |
Inka Seidel |
Inka |
Seidel |
Assuming your Excel file is set up as above:
A2 = Column with the full name
B2 = Empty column for the first name
C2 = Empty column for the last name
To format the first name, follow these steps:
-
Insert the following formula into B2:
=LEFT(A2, FIND(" ", A2))
-
Now the first name from A2 should appear in the correct format in B2.
To format the last name, follow these steps:
-
Insert the following formula into C2:
=RIGHT(A2, LEN(A2) - LEN(B2))
-
Now the last name from A2 should appear in the correct format in C2.