Splitting Field Help
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello,
I have a file that shows me sales by customers. However, the products they bought are all listed in a single field, which isn't very helpful. I attached a sample of my data in excel.
In short, if you look at column G, each of the first few orders have one product. the last row has 2. What I would love is to have this broken out so I can run product quantity analytics. Ideally This record would be broken into 2 lines. The first product on one line and the next product underneath. I'm ok if all the other fields have duplicative information.
Thanks so much in advance!
Solved! Go to Solution.
- Labels:
- Tips and Tricks
- Transformation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Anweinbe
The attached workflow parses your column G product(s) twice:
- the first time uses the Text to Columns tool to parse the Product(s) column using comma between products, and split the columns in rows.
- the second time uses the Text to Columns tools parsing the Product(s) column on the × character (note, it looks like an x by it is not), spliting the column in two columns.
- It rename the column Product-1 to Quantity and Product-2 to Product using a select tool.
hth
Arnaldo
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Arnold thank you so much! The output is perfect. I just reviewed the workflow to follow along. Very straightforward! I never new I could "split to rows".
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@Anweinbe indeed, the Text to Column tool is great