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.

Haytham Amairah

Silver Contributor

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.

Dan Kish

Copper Contributor

Mar 10, 2018

Cheers Haytham,

This method works perfectly (especially if new categories are added in the future where they will simply default to "10" characters, which is what i want) - thanks

Forum Discussion

LEFT formula based on adjacent cell text and pertaining value

Resources