Formula to find text before numbers in a string

Hi There!

I am looking for a formula to help us sort and organize data by using brand IDs & batch #'s. To keep track of our products, we name them with a "Batch ID". This includes a letter brand code, followed by a numeric string. For example, we will have a batch ID of AB1234. This includes the brand ID of "AB", and the batch # "1234" of that brand.

What I am looking for is a formula that will pull out the letters before the text. So in the above example, I could have a column formula that will pull out "AB". Most of our batch IDs are 2 letters, but some are 3, and some are 4. So we could have a batch # of XYZ345...So I couldn't just use the LEFT() function with the same position for all batches.

I tried using a =LEFT([Batch ID],MIN(FIND({0,1,2,3,4,5,6,7,8,9},[BATCH ID]&"012345679"))-1) which has worked for us in excel, but does not seem to work when put into smartsheet.

Any help is much appreciated!


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!