ranjith2523
Board Regular
- Joined
- Apr 29, 2014
- Messages
- 137
- Office Version
- 365
Hello Experts,
Again, i am seeking your help on a formula which breaking my head. I almost wrote the formula but it is not working as expected.
Initially what i did is, In column A (from cell A2) i just used the Length formula to get the length of the product from column B (from cell B2).
Then i applied the below formula in from Cell C2 to get the expected results. It is almost working but not working for the products like "3XLK462R-A21" hence seeking your help to simplify my formula and get the desired result.
=IF(OR(B2="",B2=" ",B2="0"),"",IF(OR(A2>=11),IF(MID(B2,7,1)="R",LEFT(B2,6)&RIGHT(B2,4)),IF(RIGHT(B2,1)="R",LEFT(B2,A2-1))))
Thanks for all your help in advance.
Regards,
Ranjith
Again, i am seeking your help on a formula which breaking my head. I almost wrote the formula but it is not working as expected.
Initially what i did is, In column A (from cell A2) i just used the Length formula to get the length of the product from column B (from cell B2).
Then i applied the below formula in from Cell C2 to get the expected results. It is almost working but not working for the products like "3XLK462R-A21" hence seeking your help to simplify my formula and get the desired result.
=IF(OR(B2="",B2=" ",B2="0"),"",IF(OR(A2>=11),IF(MID(B2,7,1)="R",LEFT(B2,6)&RIGHT(B2,4)),IF(RIGHT(B2,1)="R",LEFT(B2,A2-1))))
Rmkt Skus.xlsm | |||||
---|---|---|---|---|---|
F | G | H | |||
1 | Products | Expected Result | Comments | ||
2 | AD058AR#001 | AD058A#001 | F2 cell part number contains # and the length is greater or equal to 11. In this case, the R should be removed which appear just before the # and rest appear as it is in cell G2. | ||
3 | 591973R-B21 | 591973-B21 | F3 cell part number contains hyphen and the length is greater or equal to 11. In this case, the R should be removed which appear just before the last occurrence of hyphen and rest should appear as it is in cell G3. | ||
4 | A4882AR | A4882A | F4 cell part number does not contain # or hyphen so the expectation is to remove the R from the end and rest should appear as it is in Cell G4. | ||
5 | 640QR-B3 | Base | F5 cell part number length is less then 11 so the result should appear as "Base" | ||
6 | A58AR#001 | Base | F6 cell part number length is less then 11 so the result should appear as "Base" | ||
7 | 3R-A0824-AA | Base | F7 Cell part number does not have R just before the last occurrence of hyphen hence it should be retain as "Base" | ||
8 | 3R-A0824R-AA | 3R-A0824-AA | F8 cell part number contains hyphen and the length is greater or equal to 11. In this case, the R should be removed which appear just before the last occurrence of hyphen and rest should appear as it is in cell G8. | ||
9 | 2T-SC008-SR | Base | F8 cell contains hypen and No R found just before the last occurrence of hyphen hence it should be retains as "Base" | ||
Sheet1 |
Thanks for all your help in advance.
Regards,
Ranjith