String search?

[tony0682]

I'm trying to just get the product name from the following separated from the other information on the right. I have a lot of products listed that are of different lengths.

BEAR PETER BROWN 22OZ BT 12LSE
BEAR RACER 5 IPA 12OZ BT 4/6
BEAR RACER 5 IPA 22OZ BT 12LSE


I want to be able to look at the line and just show the information once it seems a number. Lets say that info is in A1:A3. In B1:B3, it would just show Bear Peter Brown, Bear Racer, and Bear Racer. How would I go about searching for a number in a string and then just showing the info before the number?

 

[Aladin Akyurek]

I'm trying to just get the product name from the following separated from the other information on the right. I have a lot of products listed that are of different lengths.

BEAR PETER BROWN 22OZ BT 12LSE
BEAR RACER 5 IPA 12OZ BT 4/6
BEAR RACER 5 IPA 22OZ BT 12LSE


I want to be able to look at the line and just show the information once it seems a number. Lets say that info is in A1:A3. In B1:B3, it would just show Bear Peter Brown, Bear Racer, and Bear Racer. How would I go about searching for a number in a string and then just showing the info before the number?

Try...

=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

 

[tony0682]

Perfect!

Now.. The other part.. How do I get everything after the first number it sees to the right?

 

[Jonmo1]

Assuming Aladin's formulas is in B1

In C1 put

=TRIM(SUBSTITUTE(A1,B1,""))

 

[tony0682]

Excellent.

Thanks for your help!

 

[T. Valko]

Try...

=TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

We can use one less function call...

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-2)