How to parse 2 columns at same time? How to deal with delimeters with nothing inbetween?

[lynnlinnewtoalteryx]

My question looks a bit complex, and I'll try to make it clear what I want to achieve. I have a dataset like this:

RecordID	Route	ClassType
1	HKG-LHR,GLA-LHR,LHR-HKG	Business/Economy/Business
2	HKG-DLC,DLC-TNA,TNA-HKG	Economy/Economy/
3	HKG-JFK,JFK-MCO,MCO-JFK,JFK-HKG	Economy///Economy

For analyse purpose, I want to parse column 'Route' and 'ClassType' so that each route and type forms one row. However, if I simply parse by 'Text to Columns' into rows, it became uneven, with more rows parsed by 'Route' and less rows parsed by 'ClassType' hence I cannot re-join them by position. The reason I've seen is that there was '//' or ending '/' in 'ClassType' which was skipped and not parsed into a new row. For example, the first 2 rows of my data hence became like below:

RecordID	Route	RecordID	ClassType
1	HKG-LHR	1	Business
1	GLA-LHR	1	Economy
1	LHR-HKG	1	Business
2	HKG-DLC	2	Economy
2	DLC-TNA	2	Economy
2	TNA-HKG	3	Economy

What could I do to solve this problem? I thought maybe I could use some other tools, like Regex, but don't know how to translate the pattern...

Thank you in advance for those who could help provide solution(s)!

Kind regards,

Lynn

[Qiu]

@lynnlinnewtoalteryx 
Hope this would help. 😁

[Pingu]

I thought it was a interesting problem. I interpreted it a bit different than Qiu, as the classtype for TNA-HKG is unknown as far as I can see and not economy.

[lynnlinnewtoalteryx]

Thank you! You're right, there should be 'unknown' ClassType inbetween 2 back-to-back '/' or end with a '/'.

[lynnlinnewtoalteryx]

Thank you! Though I'd expect another row of RecordID #2 with an unknown ClassType. It helps as well!

[Christina_H]

I think this should do it.