Question about a "LEFT" formula

Rick G

Board Regular
Joined
Sep 11, 2003
Messages
62
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
 

Some videos you may like

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.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
=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
Joined
Sep 1, 2004
Messages
55
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
Joined
Sep 11, 2003
Messages
62
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,040
Messages
5,545,676
Members
410,697
Latest member
srishtijain0708
Top