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

V

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!

[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]
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
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),"")))
 
Upvote 0
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!


tyija1995 said:
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),"")))
Click to expand...
 
Upvote 0
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.
 
Upvote 0
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?

navic said:
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.
Click to expand...
 
Upvote 0
vipin30sep said:
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!
Click to expand...

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!
 
Upvote 0
I just need the ability to perform sorting based on sizes, like Small should come in first row, medium then large and so on
Click to expand...
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.

[TABLE="width: 1050"]
<tbody>[TR]
[TD]Row/Col[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Data[/TD]
[TD]Result[/TD]
[TD]Helper[/TD]
[TD][/TD]
[TD="colspan: 2"]SIZE - named range[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Small"}][/TD]
[TD]Mr. & Mrs. Small[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Small[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Large"}][/TD]
[TD]Mr. & Mrs. Large[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]Large[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Medium"}][/TD]
[TD]Mr. & Mrs. Medium[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]Medium[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "X-Large"}][/TD]
[TD]Mr. & Mrs. X-Large[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]X-Large[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "2XL"}][/TD]
[TD]Mr. & Mrs. 2XL[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]2XL[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thanks @navic,

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



navic said:
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.

[TABLE="width: 1050"]
<tbody>[TR]
[TD]Row/Col[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Data[/TD]
[TD]Result[/TD]
[TD]Helper[/TD]
[TD][/TD]
[TD="colspan: 2"]SIZE - named range[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Small"}][/TD]
[TD]Mr. & Mrs. Small[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Small[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Large"}][/TD]
[TD]Mr. & Mrs. Large[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]Large[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "Medium"}][/TD]
[TD]Mr. & Mrs. Medium[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]Medium[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "X-Large"}][/TD]
[TD]Mr. & Mrs. X-Large[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]X-Large[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][{"name": "wedding_type", "value": "Mr. & Mrs."}, {"name": "shirt_size", "value": "2XL"}][/TD]
[TD]Mr. & Mrs. 2XL[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]2XL[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
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
 
Upvote 0
@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
[TABLE="width: 226"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Mrs. & Mrs. / Large[/TD]
[/TR]
[TR]
[TD]Mrs. & Mrs. / Medium[/TD]
[/TR]
[TR]
[TD]Mrs. & Mrs. / Small and so on for all the sizes[/TD]
[/TR]
</tbody>[/TABLE]



vipin30sep said:
Thanks @navic,

The custom sorting works! Is there a way I can do this as part of macro?
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Lookup value based on multiple criteria where one is searching a cell for specific text string
Replies
0
Views
704
alex0182828
A
J
  • Solved
VBA - Send email based on a cells value; Include other cells value's from the same line in email subject
Replies
4
Views
1K
Jibsta
J
TheMacroNoob
  • Question Question
Search Multiple Sheets for Conditional Values
Replies
1
Views
446
TheMacroNoob
TheMacroNoob
V
  • Question Question
Lookup between a group of text values and assigning numbers to it
Replies
4
Views
750
DanteAmor
DanteAmor
C
  • Question Question
How can add custom text to a cell based on a specific entry in a different cell?
Replies
4
Views
738
ChemistV700
C

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top