Automation / Formula to separate text from flight information



Is there a way to separate the text from a flight booking straight from our travel system into separate Smartsheet columns?

This is the data we receive in our travel system when booking a flight :

BA 990Y 10NOV 4 LHRBER SS1 0710 1005

I would love to be able to move this data into several columns in Smartsheet:

Flight No: BA990

Arrival Date: 10/11/23

Departure Airport: London Heathrow LHR

Arrival Airport: Berlin BER

Departure Time: 07:10

Arrival Time: 10:05

Bit of a long shot but is anything like this possible? I've tried formulas like '=Mid' formulas in Excel and they work to some extent but it doesn't format them very well.

Many thanks.


  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @justdan2

    I hope you're well and safe!

    Yes, it's probably possible. Is the pattern consistent?

    • First 6 characters, 5 characters for the date, and so on?

    I hope that helps!

    Have a fantastic week & Happy New Year!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!


    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • justdan2
    justdan2 ✭✭✭✭

    Hi Andrée,

    Thanks for your quick reply!

    The character limits are consistent throughout.

    BA 990Y 10NOV 4 LHRBER SS1 0710 1005

    Occasionally we will get the flight numbers with an extra digit but it will take the place of the space in the example above


    Flight 1: BA 990Y

    Flight 2: BA2390Y

    Both will have a maximum of 7 characters. However, I wouldn't want the 'Y' recorded as it's not part of the flight number.


  • justdan2
    justdan2 ✭✭✭✭

    Hi Andrée.

    Did you say you might be able to assist on this query?

    Any help is much appreciated!

    Thank you :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!