# Formula to find text before numbers in a string

Options
✭✭✭

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!

Cheers

• ✭✭✭✭✭✭
Options

This should be able to handle up to 4 letters before the numbers:

=LEFT([Batch ID]@row, FIND(IFERROR(IFERROR(IFERROR(IFERROR(VALUE(MID([Batch ID]@row, 1, 1)), VALUE(MID([Batch ID]@row, 2, 1))), VALUE(MID([Batch ID]@row, 3, 1))), VALUE(MID([Batch ID]@row, 4, 1))), VALUE(MID([Batch ID]@row, 5, 1))), [Batch ID]@row) - 1)

• ✭✭✭✭✭✭
Options

Will the number string always be 4 characters? If so you could use

=LEFT([Batch ID]@row, LEN([Batch ID]@row) - 4)

• ✭✭✭
Options

Hi Paul! No, unfortunately not. We have some batches that are in the 1-999 count, so we it will change based on how many batches we have made or make.

• ✭✭✭✭✭✭
Options

This should be able to handle up to 4 letters before the numbers:

=LEFT([Batch ID]@row, FIND(IFERROR(IFERROR(IFERROR(IFERROR(VALUE(MID([Batch ID]@row, 1, 1)), VALUE(MID([Batch ID]@row, 2, 1))), VALUE(MID([Batch ID]@row, 3, 1))), VALUE(MID([Batch ID]@row, 4, 1))), VALUE(MID([Batch ID]@row, 5, 1))), [Batch ID]@row) - 1)

• ✭✭✭
Options

It worked!!! Thank you so much!! 😃

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭
Options

Are there no plans to add the TEXTBEFORE() and TEXTAFTER() functions?

• ✭✭✭✭✭✭
Options

@CAS the CSA Those would be nice functions to have. Feel free to take a look through the Product Ideas tab at the top of this page to see if the idea has already been submitted. If it has, you can add your vote to it. If it has not, you can submit the idea yourself.

Either way, feel free to post a link to the idea here so that others searching for a similar solution can easily add their votes to the idea.

• ✭✭✭
Options

stop!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!