Extract text from an alpha numeric value

[aquad6]

Hey there!!
I am looking for a formula that will extract the base Sku from a value to the right of a cell. For example If I type MON345CD in Cell B1, I would like A1 to Populate with MON345, or if it is MON345A I need it to populate A1 with MON345. Some of my skus have one letter at the end, some have multiple letters at the end. I need it to extract the text before the alpha characters after the last numeric character. Thank you!!

 

[Rick Rothstein]

Give this formula a try...

=LEFT(B1,1+LEN(TEXTBEFORE(B1,SEQUENCE(10,,0),-1)))

 

[aquad6]

Give this formula a try...

=LEFT(B1,1+LEN(TEXTBEFORE(B1,SEQUENCE(10,,0),-1)))

This worked perfect! Could you tell me how to put an if error formula to hide the #Value?

 

[Joe4]

This worked perfect! Could you tell me how to put an if error formula to hide the #Value?

Maybe just try wrapping it in an IFERROR formula, i.e.

=IFERROR(LEFT(B1,1+LEN(TEXTBEFORE(B1,SEQUENCE(10,,0),-1))),"")

 

[aquad6]

Maybe just try wrapping it in an IFERROR formula, i.e.

=IFERROR(LEFT(B1,1+LEN(TEXTBEFORE(B1,SEQUENCE(10,,0),-1))),"")

Amazing!! Thank you!

 

[Joe4]

You are welcome.
Glad we were able to help.

Please note that when marking a post as the solution, you should mark the original post containing the solution, not your own psot acknowledging that another post was the solution.
I have made the update for you on this thread.

 

[Tom.Jones]

Hey there!!
I am looking for a formula that will extract the base Sku from a value to the right of a cell. For example If I type MON345CD in Cell B1, I would like A1 to Populate with MON345, or if it is MON345A I need it to populate A1 with MON345. Some of my skus have one letter at the end, some have multiple letters at the end. I need it to extract the text before the alpha characters after the last numeric character. Thank you!!

If it is always about the first 6 characters, then it would be simpler:
=LEFT(B1, 6)