Pull back multiple values from a block of text

Christa Brown

Hello!

I am thinking this may be a lost cause but thought I'd ask. So I currently own several sheets where Quality Analysts listen to calls and leave a block of text. I've built a lot of tables, data trending, etc.. off those variables from a Column that is a Drop Down - Multiple Values column, but it requires that I read their commentary and then select the appropriate variables. I'm wondering if there is a formula that anyone has that can pull out multiple words/references from a single column/row to a single column/row.

Example of the text below and the variables column:

The block of text is under "DAQ comments" and the Word/Phrase I'd need the formula to pull back is in the "Variables of Concern" column.

I've tried a few things like a HAS and CONTAINS but it's only successful at pulling back ONE of the key terms. For instance I've tried "=IF(CONTAINS("FCRA") = "True", "FCRA, IF(Contains("Effective Date") = "True", "Eff. Date", "") But it only pulls back FCRA and doesn't check for the next variables. Ideally if the block of text had both.. I'd want both FCRA and Eff. Date pulled back.

Any and all recommendations welcome!

Leibel S

https://community.smartsheet.com/discussion/comment/297072#Comment_297072

Sorry, missed something:

=JOIN(COLLECT({LSC Variables of Concern VLOOKUP Range 1},{LSC Variables of Concern VLOOKUP Range 1}, CONTAINS(@cell, [DAQ Comments]@row)), CHAR(10))

Andrée Starå

Hi @Christa Brown

I hope you're well and safe!

To add to Bassam's excellent answer.

You can use CHAR(10) to delimiter between the values to get the Multi-selection to work.

Make sense?

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Bassam Khalil

Hi @Christa Brown

Hope you are fine, please try the following formula i designed it for 3 criteria and you can ad mutch as you need.

=IF(CONTAINS("FCRA", [DAQ Comments]@row), "FCRA", "") + " / " + IF(CONTAINS(" Effective Date", [DAQ Comments]@row), "Effective Date", "") + " / " + IF(CONTAINS(" Delivery", [DAQ Comments]@row), "Delivery", "")

the following screenshot shows the result:

Leibel S

@Christa Brown

Assuming you want to lookup the DAQ Comments field and want to return a list of all the codes found in there:

1. Create (or if you have already) a list of your "Variables of Concern" in a separate sheet.
2. Then your formula in the "Variables of Concern" field would be:

=JOIN(COLLECT({Cross Sheet to Variables of Concern list}, CONTAINS(@cell, [DAQ Comments]@row)), CHAR(10))

Make sure to name the cross sheet reference however you create it in your sheet

Christa Brown

@Leibel S - I really like your approach because I think it's less formula building for me. I created a reference sheet for the variables and used the formula below that you provided; however, I'm getting an INCORRECT ARGUMENT error. Thoughts?

=JOIN(COLLECT({LSC Variables of Concern VLOOKUP Range 1}, CONTAINS(@cell, [DAQ Comments]@row)), CHAR(10))

Christa Brown

@Bassam Khalil and @Andrée Starå - thank you BOTH! The Formula worked and replaced the " / " with CHAR(10) and it worked perfectly.

Leibel S

https://community.smartsheet.com/discussion/comment/297072#Comment_297072

Sorry, missed something:

=JOIN(COLLECT({LSC Variables of Concern VLOOKUP Range 1},{LSC Variables of Concern VLOOKUP Range 1}, CONTAINS(@cell, [DAQ Comments]@row)), CHAR(10))

Bassam Khalil

@Christa Brown

You are welcome and I will be happy to help you anytime

Christa Brown

Thank you everyone!! @Leibel S - that worked - thank you!!

Christa Brown

HI all! Me again - So now I'm trying to change use the same JOIN COLLECT to look up the variable name and then change it. For instance, if the comment is "Annual Miles" I want it to be changed to "Mileage." I created an additional column.. right now named "Test" that pulls out the values I want to change .. but the VLOOKUP I created will only look for the first value.

Vlookup formula in the Variable Match column is: =IFERROR(IF(test@row = "", "", VLOOKUP(JOIN(COLLECT({LSC Variables of Concern VLOOKUP Range 4}, {LSC Variables of Concern VLOOKUP Range 4}, HAS(@cell, test@row))), {LSC Variables of Concern VLOOKUP Range 3}, 2, false)), "")

But it only appears to work if there's only 1 variable in the 'test" column

Leibel S

@Christa Brown

Use the same formula as before, just replace the first range in the collect function to lookup this column were you stored the changed names...

=JOIN(COLLECT({place range of 'changed' variable here},{LSC Variables of Concern VLOOKUP Range 1}, CONTAINS(@cell, [DAQ Comments]@row)), CHAR(10))

Christa Brown

@Leibel S - I did that and that formula populates the "test" column. What I am hoping to do is then use a formula to change the results in the "test" column to a different variable name. For instance in the picture above on the 2nd line. The JOIN COLLECT formula pulled back Annual Miles in the test column.. then the VLOOKUP formula in the Variable Match column brought back Mileage - which is what I wanted it to do; however, the VLOOKUP in the Variable Match column only works if there's only 1 result in the "test" column. Can I somehow make it pull back multiple results? For instance, on the 3rd line.. I'd like the VLOOKUP to reference the "test" column and pull back "Veh Use" and "Yrs Owned Vehicle"

Hope this makes sense...