Divide string at first numeric digit

[sriche01]

Hello,

I would like a formula solution to splitting a text prefix from a combination of text and numbers that make up a product's item number. Typical examples:

ACAN SIVV08
EUON LIM18-2
[TABLE="width: 126"]
<tbody>[TR]
[TD="class: xl65, width: 126"]SYRI JOS18-st[/TD]
[/TR]
</tbody>[/TABLE]
TAXU FAS50D

There are two consistent features in all the item numbers:

• They begin with 4 letters then a space
• There are 2 numeric digits that indicate a size near the end

I would like to peel the numeric part and whatever follows it off the letters in the following way:

ACAN SIVV08 would become ACAN SIVV
EUON LIM18-2 would become EUON LIM
SYRI JOS18-st would become SYRI JOS
TAXU FAS50D would become TAXU FAS

I do not want a VBA solution. I was thinking if there was a way using the RIGHT function and finding the first number to determine string length.

Any Ideas?

Many thanks!

 

[Marcelo Branco]

Assuming values in column A beginning in A2 try

B2 copied down
=LEFT(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")-1))

M.

 

[sriche01]

Thank you! So Easy and effective!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.