Extracting substrings
More often than not, data is delivered to us in a less-than-ideal state, with multiple values being held in a single field. We saw how to split data into multiple fields in the Splitting columns with multiple values recipe, in Chapter 3, Cleaning Transformations. However, splitting fields relies on the data being organized, and will never leave out any of the data. In this recipe, we'll look at extracting substrings, which will result in new fields as well. However, unlike splitting fields, we'll be able to more narrowly define what data we want to include in our new field. Furthermore, extracting substrings is non-destructive, that is, the original field will remain unaffected. In this recipe, we'll load a dataset into Tableau Prep that has a field with multiple values in it. We'll then proceed to extract each value and create separate fields for each.
Getting ready
To follow along with this recipe, download the Sample Files 7.3 folder...
