How to split text from a sentence - Please see attached dxp file
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.
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?
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:
- 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
- Do the same for the Model column
- 'Freeze' the new columns in readiness for...
- Apply an Unpivot transformation to the table to bring the Brands and Models back into one column each again