Question about a "LEFT" formula

[Rick G]

I have a long list of product codes in column A of a spreadsheet. I would like to populate column B of the same list with each supplier code for the corresponding product.

Our company recognizes the supplier code as the prefix to the product code in the following manner:

If the first four characters of any product code contains numbers OR letters only, then the supplier code is extracted from the product code by writing the formula =LEFT(A1,4) - Example: Cell A1 contains product code 11452370B. The formula described in this paragraph should yield the supplier code of 1145.

If the first three characters of any product code contains numbers AND letters, then the supplier code is extracted from the product code by writing the formula =LEFT(A1,3) - Example: Cell A1 contains product code M6JHKL6500. The formula described in this paragraph should return the value M6J.

I would like to automate this process by writing an IF statement but do not know how to do it. Any ideas would be MOST appreciated. There are bunches of us at my company who all have the same issue so a solution would be very EXCELLENT!

Thank you,

Rick

 

[Aladin Akyurek]

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH({0,1,2,3,4,5,6,7,8,9},LEFT(A2,4)))),LEFT(A2,3+ISNUMBER(LEFT(A2,4)+0)),LEFT(A2,4))

 

[sgremmo]

in italian

=SE(VAL.NUMERO(SINISTRA(A1;4)*1)=VERO;SINISTRA(A1;4);SINISTRA(A1;3))

in english

=if(VAL.NUMERO(left(A1;4)*1)=true;left(A1;4);left(A1;3))

or

=if(VAL.NUMERO(left(A1,4)*1)=true,left(A1,4),left(A1,3))

but I don't know VAL.NUMERO in english!!!

Ciao

 

[Rick G]

I thank you very deeply from the bottom of my heart!

Both examples worked magnificently!

By the way, my Italian friend, the English word you were looking for is the formula ISNUMBER.

Thanks a lot both of you!

Take care,

Rick