Formula to find text before numbers in a string

Joan Brewer

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

Paul Newcome

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)

Paul Newcome

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

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

Joan Brewer

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.

Paul Newcome

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)

Joan Brewer

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

Paul Newcome

Happy to help. 👍️

CAS the CSA

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

Paul Newcome

@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.

CAS the CSA

stop!