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!

[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "X-Large"}] <tbody> </tbody>	Mr. & Mrs. X-Large <tbody> </tbody>
[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Large"}] <tbody> </tbody>	Mr. & Mrs. Large
[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Small"}] <tbody> </tbody>	Mr. & Mrs. Small
[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "2XL"}] <tbody> </tbody>	Mr. & Mrs. 2XL
[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Medium"}] <tbody> </tbody>	Mr. & Mrs. Medium

<tbody>
</tbody>

 

[tyija1995]

Hey,

Assuming the values provided are in cells A1 to A5 try this in B1 and copy down:

B1:

CONCAT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(MID(SUBSTITUTE(A1,"{",REPT(" ",LEN(A1))),(2-1)*LEN(A1)+1,LEN(A1)),":",REPT(" ",LEN(A1))),(3-1)*LEN(A1)+1,LEN(A1)),CHAR(34),""),CHAR(125),""),CHAR(44),"")),CHAR(32),TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(MID(SUBSTITUTE(A1,"{",REPT(" ",LEN(A1))),(3-1)*LEN(A1)+1,LEN(A1)),":",REPT(" ",LEN(A1))),(3-1)*LEN(A1)+1,LEN(A1)),CHAR(34),""),CHAR(125),""),CHAR(93),"")))

 

[vipin30sep]

Wonderful!

This works!, appreciate your help. I have one more thing, I want to do sorting based on the sizes, like Small should come in 1st row, then medium, large and so on. How can that be done?
Is there a simple formula that I could use, or could you please help me understand the formula a bit. Thanks in advance!

Hey,

Assuming the values provided are in cells A1 to A5 try this in B1 and copy down:

B1:

CONCAT(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(MID(SUBSTITUTE(A1,"{",REPT(" ",LEN(A1))),(2-1)*LEN(A1)+1,LEN(A1)),":",REPT(" ",LEN(A1))),(3-1)*LEN(A1)+1,LEN(A1)),CHAR(34),""),CHAR(125),""),CHAR(44),"")),CHAR(32),TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(MID(SUBSTITUTE(A1,"{",REPT(" ",LEN(A1))),(3-1)*LEN(A1)+1,LEN(A1)),":",REPT(" ",LEN(A1))),(3-1)*LEN(A1)+1,LEN(A1)),CHAR(34),""),CHAR(125),""),CHAR(93),"")))

 

[navic]

For those forum visitors who have Excel that does not contain the CONCAT function, they may use this formula below.

If it is a variable position of specific characters

=IFERROR(LEFT(MID(A1,FIND(":",A1,FIND(" ",A1)+1)+3,256),FIND(TEXT("""","00"),MID(A1,FIND(":",A1,FIND(" ",A1)+1)+3,256))-1),MID(A1,FIND(":",A1,FIND(" ",A1)+1)+3,256)) &" "&IFERROR(LEFT(MID(TRIM(RIGHT(SUBSTITUTE(A1,":",REPT(" ",LEN(A1))),LEN(A1))),2,100),FIND(TEXT("""","00"),MID(TRIM(RIGHT(SUBSTITUTE(A1,":",REPT(" ",LEN(A1))),LEN(A1))),2,100))-1),MID(TRIM(RIGHT(SUBSTITUTE(A1,":",REPT(" ",LEN(A1))),LEN(A1))),2,100))

If it is a fixed position of specific characters

=MID(A1,37,10)&" "&IFERROR(LEFT(MID(A1,84,10),FIND(TEXT("""","00"),MID(A1,84,10))-1),MID(A1,84,10))

I hope that some users of Excel will help this.

 

[vipin30sep]

Thanks,

The 2nd formula works perfect for my scenario. I just need the ability to perform sorting based on sizes, like Small should come in first row, medium then large and so on. How can that be achieved?

For those forum visitors who have Excel that does not contain the CONCAT function, they may use this formula below.

If it is a variable position of specific characters

=IFERROR(LEFT(MID(A1,FIND(":",A1,FIND(" ",A1)+1)+3,256),FIND(TEXT("""","00"),MID(A1,FIND(":",A1,FIND(" ",A1)+1)+3,256))-1),MID(A1,FIND(":",A1,FIND(" ",A1)+1)+3,256)) &" "&IFERROR(LEFT(MID(TRIM(RIGHT(SUBSTITUTE(A1,":",REPT(" ",LEN(A1))),LEN(A1))),2,100),FIND(TEXT("""","00"),MID(TRIM(RIGHT(SUBSTITUTE(A1,":",REPT(" ",LEN(A1))),LEN(A1))),2,100))-1),MID(TRIM(RIGHT(SUBSTITUTE(A1,":",REPT(" ",LEN(A1))),LEN(A1))),2,100))

If it is a fixed position of specific characters

=MID(A1,37,10)&" "&IFERROR(LEFT(MID(A1,84,10),FIND(TEXT("""","00"),MID(A1,84,10))-1),MID(A1,84,10))

I hope that some users of Excel will help this.

 

[tyija1995]

Wonderful!

This works!, appreciate your help. I have one more thing, I want to do sorting based on the sizes, like Small should come in 1st row, then medium, large and so on. How can that be done?
Is there a simple formula that I could use, or could you please help me understand the formula a bit. Thanks in advance!

Hey,

I'm not actually sure of a good way to sort it in that manner!

The formula basically breaks down the text into sections - first using the "{" brace and then the ":" - with the sections split I then saw that the Mr & Mrs appears within the first "{" brace and the 2nd comma. It's probably going a bit of a long way around it but it should be dynamic enough to work, it does assume a fixed sub-position of the salutations and the shirt size value (i.e. after 2nd colon etc).

The extra substitutes just get rid of the "debris" such as leftover braces and such. I referenced some of these with CHAR instead of "}" - no real reason behind this to be honest, except sometimes the forums seem to omit certain characters!! Especially with less than, greater thans I have found.

The Concat just shoves the title with a space and then the size.

A small example to try understand the logic further if you're interested:

String: "Example string: hello world" (In cell A1)

=SUBSTITUTE(A1, ":", REPT(" ",LEN(A1)))

This will split the string by the colon into two parts if you will.
To reference the second part (after the first colon) use TRIM(MID(SUBSTITUTE(A1, ":", REPT(" ",LEN(A1))),(2-1)*LEN(A1)+1,LEN(A1)))

The 2 in red refers to the 2nd part, if you change this to a 1 then you'll see "Example string" instead!

 

[navic]

I just need the ability to perform sorting based on sizes, like Small should come in first row, medium then large and so on

Hi @win30sep
Based on the formulas in my post above, I have a suggestion for sort data.
Create the 'SIZE' named range (cell range 'E2:F6').
In addition, create and use the 'Helper' column 'C'.
In cell 'C2', place the following formula below (copy down).

=VLOOKUP(TRIM(RIGHT(SUBSTITUTE(B2,".",REPT(" ",LEN(B2))),LEN(B2))),size,2,FALSE)

Sorting:
Select range 'A2:C6' and click 'Custom Sort' on 'Home -> Sort & Filter'. Sort by column 'Helper'.
I hope this helped you.

Row/Col	A	B	C	D	E	F
1	Data	Result	Helper		SIZE - named range
2	[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Small"}]	Mr. & Mrs. Small	1		Small	1
3	[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Large"}]	Mr. & Mrs. Large	2		Large	2
4	[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Medium"}]	Mr. & Mrs. Medium	3		Medium	3
5	[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "X-Large"}]	Mr. & Mrs. X-Large	4		X-Large	4
6	[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "2XL"}]	Mr. & Mrs. 2XL	5		2XL	5

<tbody>
</tbody>

 

[vipin30sep]

Thanks @navic,

The custom sorting works! Is there a way I can do this as part of macro?

Hi @win30sep
Based on the formulas in my post above, I have a suggestion for sort data.
Create the 'SIZE' named range (cell range 'E2:F6').
In addition, create and use the 'Helper' column 'C'.
In cell 'C2', place the following formula below (copy down).

=VLOOKUP(TRIM(RIGHT(SUBSTITUTE(B2,".",REPT(" ",LEN(B2))),LEN(B2))),size,2,FALSE)

Sorting:
Select range 'A2:C6' and click 'Custom Sort' on 'Home -> Sort & Filter'. Sort by column 'Helper'.
I hope this helped you.

Row/Col	A	B	C	D	E	F
1	Data	Result	Helper		SIZE - named range
2	[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Small"}]	Mr. & Mrs. Small	1		Small	1
3	[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Large"}]	Mr. & Mrs. Large	2		Large	2
4	[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Medium"}]	Mr. & Mrs. Medium	3		Medium	3
5	[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "X-Large"}]	Mr. & Mrs. X-Large	4		X-Large	4
6	[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "2XL"}]	Mr. & Mrs. 2XL	5		2XL	5

<tbody>
</tbody>

 

[tyija1995]

A similar approach to the helper column @navic posted

With the strings in column A and the formulae to produce the substrings in column B, set column C (ID column) to search for the words "small", "medium", "X-large", "2XL", "large" and assign an ID to them.

The reason that order has been done is because X-large contains "large" so needs to be searched for first.

With the headers in 1st row, then C2 would become:

C2:

IF(ISNUMBER(SEARCH("Small",B2)),1,IF(ISNUMBER(SEARCH("Medium",B2)),2,IF(ISNUMBER(SEARCH("X-Large",B2)),4,IF(ISNUMBER(SEARCH("2XL",B2)),5,IF(ISNUMBER(SEARCH("Large",B2)),3,6)))))

This assigns "small" to 1, "medium" to 2, "large" to 3, "x-large" to 4, "2xl" to 5 and any other to 6 - you can then sort by this column ascending.

I only thought of doing this way thanks to Navics solution - so props to navic. The main difference in this alternative way is that no named ranges are used

 

[vipin30sep]

@navic

I'm using this formula,

MID(AR2,37,10)&" / "&IFERROR(LEFT(MID(AR2,84,10),FIND(TEXT("""","00"),MID(AR2,84,10))-1),MID(AR2,84,10))

I have one more option on the sizes. - Could you please help setting that out in this formula. The condition is for

Mrs. & Mrs. / Large

Mrs. & Mrs. / Medium

Mrs. & Mrs. / Small and so on for all the sizes

<colgroup><col></colgroup><tbody>
</tbody>

Thanks @navic,

The custom sorting works! Is there a way I can do this as part of macro?