Calculate a cell value based on a specific string in a separate cell

[vipin30sep]

Hello,

The first column contains a string of values as mentioned below . In 2nd column, I need to strip out specific info from Col 1 (as mentioned below). I tried using a formula like this - =LOOKUP(MID(AS2,12,3),{"Lar","X-L"," Sma"},{"Large","X-Large","Small"}) , but its not calculating the values correctly. Appreciate if you can help here. Thanks in advance!

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 251"]
<tbody>[TR]
[TD="width: 251"][{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "X-Large"}][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 184"]
<tbody>[TR]
[TD="width: 184"]Mr. & Mrs. X-Large[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 251"]
<tbody>[TR]
[TD="width: 251"][{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Large"}][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Mr. & Mrs. Large[/TD]
[/TR]
[TR]
[TD][TABLE="width: 251"]
<tbody>[TR]
[TD="width: 251"][{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Small"}][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Mr. & Mrs. Small[/TD]
[/TR]
[TR]
[TD][TABLE="width: 251"]
<tbody>[TR]
[TD="width: 251"][{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "2XL"}][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Mr. & Mrs. 2XL[/TD]
[/TR]
[TR]
[TD][TABLE="width: 251"]
<tbody>[TR]
[TD="width: 251"][{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Medium"}][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Mr. & Mrs. Medium[/TD]
[/TR]
</tbody>[/TABLE]

 

[navic]

I have one more option on the sizes.

I do not understand you.
If this is your result and you want to sort if there are more options then use the formula that gave @tyija1995 in post #9[TABLE="width: 226"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

 

[vipin30sep]

Hi @navic,

So this is not about sorting, it is the addition of one more condition to your original formula. I have added the condition in below formula, is there any better way to put this,

=IF(MID(AR2,37,10)="Mr. & Mrs.",MID(AR2,37,10)&" / "&IFERROR(LEFT(MID(AR2,84,10),FIND(TEXT("""","00"),MID(AR2,84,10))-1),MID(AR2,84,10)),IF(MID(AR2,37,10)="Mrs. & Mrs",MID(AR2,37,11)&" / "&IFERROR(LEFT(MID(AR2,85,10),FIND(TEXT("""","00"),MID(AR2,85,10))-1),MID(AR2,85,10)),""))

I do not understand you.
If this is your result and you want to sort if there are more options then use the formula that gave @tyija1995 in post #9[TABLE="width: 226"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

 

[navic]

is there any better way to put this

As far as I can see, you fixed the starting number in the MID formula.
Example fixed start number is 84 or 37

MID(A2;84;10)

If the conditioned cell is always the same number of characters it's okay.

I gave you two formulas in post # 4.
The first formula is for a variable number of characters
The second formula is for a fixed number of characters (in this formula you can use fixed start number)

If your last formula returns the desired result, I think you not need to change anything.
I do not know why you're in an argument "value_if_false" using the formula?
This formula below is a little shorter and uses it if it returns the expected results. Argument "value_if_false" equal "" or blank.

=IF(MID(A2,37,10)="Mr. & Mrs.",MID(A2,37,10)&" / "&IFERROR(LEFT(MID(A2,84,10),FIND(TEXT("""","00"),MID(A2,84,10))-1),MID(A2,84,10)),"")

 

[vipin30sep]

Thanks @navic,

It works. Appreciate your help as always. Actually there is a macro that I run which has this formula, it breaks if I don't add the argument to have FALSE. All good. Appreciate your help.

As far as I can see, you fixed the starting number in the MID formula.
Example fixed start number is 84 or 37

MID(A2;84;10)

If the conditioned cell is always the same number of characters it's okay.

I gave you two formulas in post # 4.
The first formula is for a variable number of characters
The second formula is for a fixed number of characters (in this formula you can use fixed start number)

If your last formula returns the desired result, I think you not need to change anything.
I do not know why you're in an argument "value_if_false" using the formula?
This formula below is a little shorter and uses it if it returns the expected results. Argument "value_if_false" equal "" or blank.

=IF(MID(A2,37,10)="Mr. & Mrs.",MID(A2,37,10)&" / "&IFERROR(LEFT(MID(A2,84,10),FIND(TEXT("""","00"),MID(A2,84,10))-1),MID(A2,84,10)),"")