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

vipin30sep

New Member
Joined
Feb 14, 2019
Messages
20
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>
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
Hey,

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

B1:
Code:
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

New Member
Joined
Feb 14, 2019
Messages
20
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:
Code:
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

Board Regular
Joined
Jun 14, 2015
Messages
227
Office Version
2013
Platform
Windows
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
Code:
=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
Code:
=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

New Member
Joined
Feb 14, 2019
Messages
20
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
Code:
=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
Code:
=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

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jun 14, 2015
Messages
227
Office Version
2013
Platform
Windows
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).
Code:
=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/ColABCDEF
1DataResultHelperSIZE - named range
2[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Small"}]Mr. & Mrs. Small1Small1
3[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Large"}]Mr. & Mrs. Large2Large2
4[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Medium"}]Mr. & Mrs. Medium3Medium3
5[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "X-Large"}]Mr. & Mrs. X-Large4X-Large4
6[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "2XL"}]Mr. & Mrs. 2XL52XL5

<tbody>
</tbody>
 

vipin30sep

New Member
Joined
Feb 14, 2019
Messages
20
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).
Code:
=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/ColABCDEF
1DataResultHelperSIZE - named range
2[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Small"}]Mr. & Mrs. Small1Small1
3[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Large"}]Mr. & Mrs. Large2Large2
4[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Medium"}]Mr. & Mrs. Medium3Medium3
5[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "X-Large"}]Mr. & Mrs. X-Large4X-Large4
6[{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "2XL"}]Mr. & Mrs. 2XL52XL5

<tbody>
</tbody>
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
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:
Code:
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

New Member
Joined
Feb 14, 2019
Messages
20
@navic

I'm using this formula,

Code:
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,748
Messages
5,446,261
Members
405,392
Latest member
Steveoaktree1977

This Week's Hot Topics

Top