How to split text from a sentence - Please see attached dxp file

Hi All,

Please review attached dxp file. I wanted to rearrange the columns brand and model, extracting the brand and model name.


the second cross table shows how I wanted my column to be rearranged.


Kindly help.



Binary Data text_splitting.dxp154.99 KB


Hi Chris,

the brand name can go up to 5,

Thank you,

Arjund - Apr 07, 2017 - 4:13am ::

Will there only be a max of 2 brands in a single cell? If not, is there a known max or could it be any number?

Sean Riley - Apr 06, 2017 - 10:37am ::

Hi Chris,

Yes, some of the data comes in this format where the two brands are given in the same row.

Are there any options we can split these as well?

Thank you,

Arjund - Apr 05, 2017 - 3:43am ::

This is relatively straightforward except for the problem created by row 4 where you have two brands/models in one column? You want these split into two rows? Are there examples in your data where there might be MORE THAN two models in one cell?

Without going into detail (until you explain the full scope of the problem) my suggestion would be:

  1. Split the Brand column into Brand 1, Brand 2, Brand X columns etc. This can be done using Find expression and a combination of text functions (usually Mid) + maybe regex
  2. Do the same for the Model column
  3. 'Freeze' the new columns in readiness for...
  4. Apply an Unpivot transformation to the table to bring the Brands and Models back into one column each again
chrisdunhill - Apr 04, 2017 - 4:05am ::
View More Comments + Add a Comment

(1) Answer