# Question about a "LEFT" formula

#### Rick G

##### Board Regular
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

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

##### MrExcel MVP
=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

##### Board Regular
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

##### Board Regular
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

Replies
3
Views
77
Legacy 456155
L
Replies
4
Views
102
Replies
11
Views
177
Replies
3
Views
86
Replies
3
Views
50