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

#### vipin30sep

##### New Member
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>

### 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
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
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
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
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
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
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/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

##### New Member
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/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

##### Well-known Member
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
@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?