Initial Advice
Preparing CSV files can be tricky, as each individual file often requires specific formatting adjustments. If you're having trouble getting your CSV into the correct format, a helpful approach is to take a screenshot of your file, upload it to a tool like ChatGPT, and describe the format you need.
This allows you to receive tailored guidance, including specific formulas to resolve your issue. Below, however, you’ll find examples of how to fix some of the most common CSV formatting problems.
We will use Google Sheets for this example, but the exact same steps and formulas can be applied in Excel.
How to format phone numbers
To ensure that your contacts are imported into Superchat with the correctly formatted phone numbers, you need to format them in Excel/Google Sheets.
Note: In this article we use +49, Germany's country code, as placeholder. However, any other country code can be treated exactly the same way.
If the phone number starts with 0, 0049, 1, or 49 in the CSV
| A | B | C | D |
1 | First name | Last name | Phone number ❌ | Phone number ✅ |
2 | Maurizio | Superchat | 0156784321001 | +49156784321001 |
3 | Oliver | Superchat | 0049156784321002 | +49156784321002 |
4 | Inka | Superchat | 156784321003 | +49156784321003 |
5 | Juri | Superchat | 49156784321004 | +49156784321004 |
This table shows the finalised version of the CSV, but it is likely at the moment you only have columns A, B & C. You can start by adding another column next to the phone number column (this usually makes formulas easier).
Then, if the phone numbers are formatted in one of the ways shown in column C, you can change them to the correct format using the following formula:
=IF(LEFT(D3;1)="+";D3;IF(LEFT(D3;2)="00";"+"&RIGHT(D3;LEN(D3)-2);"+"&D3))
To insert the formula, click on the cell where you would like the altered version to be and paste it in the box shown:
In this case, we are changing the format of cell D3, which you will need to adjust yourself to the relevant cell you want to change.
Once you have the correct formula that works for that row, you can click the bottom right corner of the cell and drag it down to make it applicable for every row (in Excel this will be a small plus symbol).
If phone number contains characters
| A | B | C | D |
1 | First Name | Last Name | Phone Number ❌ | Phone Number ✅ |
2 | Maurizio | Superchat | +49 15678 4321001 | +49156784321001 |
3 | Oliver | Superchat | +4915678/4321002 | +44156784321002 |
4 | Inka | Superchat | +49AA1567843210 | +49156784321003 |
In this case, you don't need to do anything further, all characters will be ignored during upload. The important thing is that the number starts with +49.
Note: If you cannot make the + appear before the number, you can add an apostrophe before the plus (meaning you would write '+) to stop it being registered in Google sheets as a function.
Format first and last names
First name & last name separation
| A | B | C |
1 | Name ❌ | First Name ✅ | Last Name ✅ |
2 | Maurizio Blötscher | Maurizio | Blötscher |
3 | Oliver Klötzer | Oliver | Klötzer |
4 | Inka Seidel | Inka | Seidel |
Assuming your Google Sheets file is set up as above:
A2 = Cell with the full name
B2 = Empty cell for the first name
C2 = Empty cell for the last name
To format the first name, follow these steps:
Insert the following formula into B2:
=LEFT(A2,SEARCH(" ",A2))Now the first name from A2 should be displayed in the correct format in B2
If you then want 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 be displayed in the correct format in C2