Is this even possible? or am I barking up the wrong tree??

[lynzlou]

Hello all,

I am about 17 min into Get & Transform and Power Query so I am not even sure what I want to do is possible.. thought I would ask the experts before going down the google rabbit hole.

In my job, I have to pull flight info from an airport website then report on how many domestic or international flights are departing between certain hours (ie 0600-0649). Currently I just copy and paste the info from the site and end up with this ugly string of text in excel:

05:00 Dec10WestJetWS518,LA5203,DL7151,AA8138Toronto,ON On Time - 05:00 6

for about about 50 different flights, on different airlines so each row is a different number of characters. What I need to extract from each row is the departure time (ie 05:00) the city (ie: Toronto, ON) and the gate number (ie: 6) - keeping in mind the time, city and gate number is different in each row. Is there a query or formula or some magic beans that will extract that info for me???

[TABLE="width: 77"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Misca]

It might not be pretty but it's doable if the text structure stays the same so that you can always split the text the same way:

If the last space is always before the gate number just split using the rightmost space as the delimiter.
After that you can do the same with the time using the leftmost or rightmost space as the delimiter depending which one you're after.

The city is a bit more tricky to get because there's no direct delimiter.

I'd split the text twice using the rightmost comma as the delimiter so that I'd end up with "AA8138Toronto" as the first part and "ON On Time..." as the second. Then I'd add a custom column where I'd combine Text.End(FirstPart, Text.Length(FirstPart)-6) and Text.Start(SecondPart,2) with ", " in between.

This one only works if the City name always starts from the 7th character after the comma. If not, I'd try replacing all the numbers from 0 to 1 with nothing to get rid all the numbers and then start from the third character.