The base for the fixing is the first attachment.
The desired outcome will then look like the second attachment. This file can serve as a guideline for your import file.
Below, we will explain how to fix most of the columns in your import file. You will need to know only these three formulas to do so:
- Text to Columns
- Sort and Filter/Find and Select
- Combine text formula
Text to columns formula
This formula can be applied to fix columns with addresses, email addresses, phone numbers, account managers, related contacts (or all other columns with multiple values).
- Insert on the right as many new columns as the max number of email addresses you have in the same cell, select your email address column > Go to Data > Select the option Text to Columns:
- Choose the option Delimited.
- Press Next > Choose your delimiting symbol from the list (e.g. comma, semicolon, etc.) or type your delimiting symbol next to Other.
4. Press Next to continue > Choose the option General for the column Data format and check the new columns that will be created in the Data preview section:
5. Press Finish. Voilà, it’s done!
- In step 4 you have to choose the option Text for the column Data format.
- Column phone number: If you have two or more telephone numbers in the same cell, you can separate them with the Text to Columns formula.
Sort and filter/Find and select
This formula can be applied to columns with countries, languages, payment terms, sectors (or all other columns in which you need to replace values).
- Select the column with the country.
- Click on sort and filter and select Filter:
3. You will see a dropdown button on the column you selected, click on it to get a list of all the different countries in the column.
4. In order to replace extended names of countries by the 2 letters ISO code you need to use Find and Select.
5. Click on Find and Select and choose Replace:
6. Enter the name of a country in Find what field, enter the corresponding ISO code in the Replace with field and then click on Replace all:
7. Repeat step 6 for each country.
Combine tags from two or more cells in one cell
- Insert a new column on the right hand side.
- Enter = in the first cell of your new column, select the first cell of the first tag column, type the string &“,”& then select the second cell of the second tag column and press Enter to confirm.
- To apply the formula to the whole column, select the cell with the formula, place the mouse on the lower right corner until you see a black + symbol.
4. Click on the + symbol and drag it down. Your column will look like this below: