Need Help On A Formula

[ranjith2523]

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))))

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

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[ranjith2523]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi Fluff,

Good to see you again !!!

Sorry for that, i am using Excel 365 and updated it in "Account Details". Are you not able to see my spread sheet ?

Please let me know what can i do to get the solution.

Thanks for all your help in advance.

Regards,
Ranjith

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B		D
1	Products	Expected Result
2	AD058AR#001	AD058A#001		AD058A#001
3	591973R-B21	591973-B21		591973-B21
4	A4882AR	A4882A		A4882A
5	640QR-B3	Base		Base
6	A58AR#001	Base		Base
7	3R-A0824-AA	Base		Base
8	3R-A0824R-AA	3R-A0824-AA		3R-A0824-AA
9	2T-SC008-SR	Base		Base
Master

Cell Formulas
Range	Formula
D2:D9	D2	=LET(L,LEN(A2),sub,FIND("||",SUBSTITUTE(A2,"-","||",L-LEN(SUBSTITUTE(A2,"-","")))),IF(L<11,IF(RIGHT(A2)="R",LEFT(A2,L-1),"Base"),IF(MID(A2,IFERROR(FIND("#",A2)-1,1),2)="R#",SUBSTITUTE(A2,"R#","#"),IF(MID(A2,sub-1,1)="R",REPLACE(A2,sub-1,1,""),"Base"))))

 

[ranjith2523]

Thanks for that, how about

+Fluff 1.xlsm
	A	B		D
1	Products	Expected Result
2	AD058AR#001	AD058A#001		AD058A#001
3	591973R-B21	591973-B21		591973-B21
4	A4882AR	A4882A		A4882A
5	640QR-B3	Base		Base
6	A58AR#001	Base		Base
7	3R-A0824-AA	Base		Base
8	3R-A0824R-AA	3R-A0824-AA		3R-A0824-AA
9	2T-SC008-SR	Base		Base
Master

Cell Formulas
Range	Formula
D2:D9	D2	=LET(L,LEN(A2),sub,FIND("||",SUBSTITUTE(A2,"-","||",L-LEN(SUBSTITUTE(A2,"-","")))),IF(L<11,IF(RIGHT(A2)="R",LEFT(A2,L-1),"Base"),IF(MID(A2,IFERROR(FIND("#",A2)-1,1),2)="R#",SUBSTITUTE(A2,"R#","#"),IF(MID(A2,sub-1,1)="R",REPLACE(A2,sub-1,1,""),"Base"))))

This is what exactly i need.. You are always helpful to me.. Thanks a lot from bottom of my heart

 

[Fluff]

You're welcome & thanks for the feedback.