Extract Text from Cell Help

Good morning,

This should be a simple one but I can't get my head around this formula...

The cell contains the following text:

F0101 - 370001234

I would like to extract into a different cell all characters before the first " ". In this instance it is F0101.

Where I am struggling is because the text I want to extract varies in number of characters. For example, it could be:

GHA750WR - 370001234

In the above example I would want the cell to display GHA750WR.

I appreciate any support you can give with this.


Thank you

Answers

  • Amit Wadhwani
    Amit Wadhwani ✭✭✭✭✭✭

    Hi Lee

    To replicate your scenario, I created a column titled "Source" which contains the source data and two columns titles Left and Right. The left one should contain text before the - and right should contain text after the -. I put below formulae in the left and right columns respectively.

    =LEFT(Source@row, FIND(" -", Source@row) - 1)

    =RIGHT(Source@row, LEN(Source@row) - FIND("- ", Source@row))

    You need to replace Source with the name of the column in your sheet. This should give you the expected results.

    Thanks

    Amit

    Best Regards

    Amit Wadhwani, Smartsheet Community Champion

    Smartsheet CoE, Ignatiuz Software, Exton, PA

    https://www.linkedin.com/in/amitinddr/


    Did this answer help you? Show some love by marking this answer as "Insightful 💡" or "Awesome ❤️" and "Vote Up ⬆️"

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!