# 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

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
=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))

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

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
4
Views
227
Replies
5
Views
251
Replies
1
Views
212
Replies
5
Views
221
Replies
12
Views
957

1,221,127
Messages
6,158,101
Members
451,464
Latest member
Holden3

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back