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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
=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
 

Forum statistics

Threads
1,141,665
Messages
5,707,707
Members
421,524
Latest member
Bharath99

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top