Text to columns delimited by # character

Hi, I have a column I need to separate from 1 column into 2, delimited by the # character. The output (new column) is consistently 6 characters, but the amount of characters preceding the # always varies. I know how to do this in Excel but would love do this with a formula in Smartsheet. Thanks for any tips in advance!

Example cell data

Column 1: Joe Drives A Slow Car #123456 needs to become

Column 1: Joe Drives A Slow Car

Column 2: 123456



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!