Forum Discussion

Copper Contributor

Mar 09, 2018

Solved

LEFT formula based on adjacent cell text and pertaining value

Hi, i have a report which consists of thousands of entries in column A and B only (column C and D do not exist in the report). The example below shows all the categories in Column B within th...

Formulas & Functions

Need Help

Haytham Amairah
Mar 09, 2018
Dan,

I've understood from your explanation that you want to extract from column A a text with a set of characters based on a category:

6 characters with the categories: 100 Jeans, 105 Jeans Kids, 110 Pants.

10 characters with the others.

If so, you don't have to create a helper column (column c), you can identify the set of characters inside the formula in cell D2 as follows:

=LEFT(A2,IF(OR(B2="100 Jeans",B2="105 Jeans Kids",B2="110 Pants"),6,10))

Hope that helps.

Jamil

Bronze Contributor

Mar 09, 2018

Hello Dan,

Please open the attached workbook.

I have put the formula there. So, you do not need to have the helper column of C.

here is the formula. So all you have to change is the A2 and B2 in the below formula, in case your actual data are in different columns.

=LEFT(A2,VLOOKUP(B2,{"100 Jeans","6";"105 Jeans Kids","6";"110 Pants","6";"120 Denim Jackets","10";"130 Jackets","10";"140 T-shIrts","10";"150 Shirts","10";"160 Sweatshirts","10";"170 Knits","10";"180 Accessories","10";"190 Underwear","10";"990 Miscellaneous","10"},2,0))

JAMIL.xlsx10 KB

Dan Kish

Copper Contributor

Mar 10, 2018

Thanks Jamil,

this worked for me also - thanks for your time - appreciated.

Dan

Jamil
Bronze Contributor
Mar 10, 2018
You are welcome.

you can further simplify Haytham's formula to this. so instead of 3 B2 references you can just have one.

=LEFT(A2,IF(OR(B2={"100 Jeans","105 Jeans Kids","110 Pants"}),6,10))