Overview
In this how-to article, we are describing how to use DataBridge Transformation to split an input column into multiple output columns.
...
- A (demo) subscription of Nebula DataBridge, to sign - up, follow this this URL: https://my-databridge.infobridgeuniverse.com/Account/SignUp
- A SuperOffice CRM login name with administrator rights
...
Gabriel | José | de | la | Concordia | García | Márquez |
|---|---|---|---|---|---|---|
0 | 1 | 2 | 3 | 4 | 5 | 6 |
-7 | -6 | -5 | -4 | -3 | -2 | -1 |
First segment index | Last segment index | |
|---|---|---|
First word | 0 | 0 |
First three words | 0 | 2 |
Last three words | -3 | -1 |
First word to next to last word | 0 | -2 |
Second word to last word | 1 | -1 |
Last word | -1 | -1 |
In practice, if you need to split a full name into first name and last name, you have to think about the fact that many people have compound surnames, which means that the field will be split into more than two segments. You can decide that the first word will always be the first name, and all the rest shall be placed in the last name output field.
To do so, you have to create two split transformations. In both transformations, you set the separator as [:space:]. In the “first name” transformation, select segments from First to First and in the “last name” transformation, select segments from Second to Last. Voilà! All your contacts with a compound surname can now have their family name correctly registered in your system. In practice, if you need to split a full name into first name and last name, you have to think about the fact that many people have compound surnames, which means that the field will be split into more than two segments. You can decide that the first word will always be the first name, and all the rest shall be placed in the last name output field.
Examples for splitting person full name
Enter the values below to achieve the needed split functionality.
...
Value in import or export column | Output Field Name | Separator | Segments from | To | Ignore field if not splittable | Remark |
|---|---|---|---|---|---|---|
Pierre van Mever | First name | [:space:] | first (index 0) | first (index 0) | see details below | This will only put the first name in your output field |
Pierre van Mever | Last name | [:space:] | second (index 1) | last (index -1) | see details below | This will put the middle name and last name in your output field |
Example for splitting street address
Split field transformation sample: Split Street address in separate street name and house number. You need to create two split transformations with the same separator but different selected segments
Value in import or export column | Output Field Name | Separator | Segments from | To | Ignore field if not splittable | Remark |
|---|---|---|---|---|---|---|
Jan de Groot laan 4 | Street name | [:space:] | first (index 0) | next to last (index -2) | see details below | The entire street name except the number will be in your output field |
Jan de Groot laan 4 | House number | [:space:] | last (index -1) | last (index -1) | see details below | This will only put the number in your output field |
Example for splitting date field
Split field transformation sample: Split date field in separate date and time field. In this case, a consultant did not want the time from the updatedDate field exported next to the date. Note: Although we now have a separate option to select the date/time format of your choice when exporting (more info), we did want to show you this example.
- Choose the new transformation Split field
- In the split field setting page select the date field that must be cut so it doesn’t contain the time anymore.
- In the Output field name enter a name for a new column that contains the splitted split date
- In separator add the split delimiter needed, in this case [:space]
- The segment from and to can be left empty for a date-time split. (When you have several splits as a result (e.g. “Pierre van Mever” will have 3 segments) each segment can be assigned to a separate field in these selectors)
- Click save and finish the export profile.
...