Skip to main content
All CollectionsContacts
How To Prepare Your CSV Import Using Excel
How To Prepare Your CSV Import Using Excel

How to get telephone number, first name, and last name in the correct format

Support Superchat avatar
Written by Support Superchat
Updated over a year ago

Overview


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:

  1. Insert the following formula into D2: =SUBSTITUTE(C2, "0", "+49", 1)

  2. 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:

  1. Insert the following formula into D2: =SUBSTITUTE(C2, "0049", "+49", 1)

  2. 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:

  1. Insert the following formula into D2: =LINK("+49", C2)

  2. 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:

  1. Insert the following formula into D2: =LINK("+", C2)

  2. 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:

  1. Insert the following formula into B2: =LEFT(A2, FIND(" ", A2))

  2. Now the first name from A2 should appear in the correct format in B2.

To format the last name, follow these steps:

  1. Insert the following formula into C2: =RIGHT(A2, LEN(A2) - LEN(B2))

  2. Now the last name from A2 should appear in the correct format in C2.

Did this answer your question?