Parse-ing

Hi Brain Trust

I have a sheet that has two Compliance & Training Columns

1/ Job Requirement

2/ Candidate Compliance Check

3/ Helper columns that reports the Count difference between 1&2 - this then triggers conditional formatting and a symbol Column

I have been asked to - when drop downs are select in the job Requirement Column( can be upto 20 requirements) that each requirement get parse out into individual Columns(yet to be added

Best Answers

Answers

  • Paul Newcome
    Paul Newcome Community Champion

    Assuming "!" and "~" are not going to be found in any of the options (if it is just change it in the below to something that isn't), you can use:

    =IFERROR(MID("!" + SUBSTITUTE([Column Name]@row, CHAR(10), "!") + "!", FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Column Name]@row, CHAR(10), "!") + "!", "!", "~", 1) + 1, FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Column Name]@row, CHAR(10), "!") + "!", "!", "~", 2) - (FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Column Name]@row, CHAR(10), "!") + "!", "!", "~", 1) + 1)), "")

    In the above you will see three numbers in bold in the order of 1, 2, then 1 again. The 1 is going to indicate that you are wanting to pull the first entry. The 2 is simply 1 plus the entry number. So to pull the second entry, you would use 2, 3, 2. Third entry would use 3, 4, 3. So on and so forth.

  • Hi Paul

    =IFERROR(MID("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", "!", "~", 1) + 1, FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", "!", "~", 2) - (FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", "!", "~", 1) + 1)), ""))))

    But I get a #incorrect Argument Set :-) and it keeps adding extra brackets at the end

  • Paul Newcome
    Paul Newcome Community Champion
    Answer ✓

    Give this a try:

    =IFERROR(MID("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", "!", "~", 1)) + 1, FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", "!", "~", 2)) - (FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", "!", "~", 1)) + 1)), "")

  • Hi Paul That worked great!!

    to make sure my blonde logic is working right - the second Item would need this whole piece

    FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Column Name]@row, CHAR(10), "!") + "!", "!", "~", 2 ) + 1, FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Column Name]@row, CHAR(10), "!") + "!", "!", "~", 3) - (FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Column Name]@row, CHAR(10), "!") + "!", "!", "~", 2) + 1)), ""), FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Column Name]@row, CHAR(10), "!") + "!", "!", "~", 3) + 1, FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Column Name]@row, CHAR(10), "!") + "!", "!", "~", 4) - (FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Column Name]@row, CHAR(10), "!") + "!", "!", "~", 3) + 1)), "")

  • Paul Newcome
    Paul Newcome Community Champion

    Not quite. It would be the same formula as before, and you would just change the numbers. You wouldn't add anything.

    First selection:

    =IFERROR(MID("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", "!", "~", 1)) + 1, FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", "!", "~", 2)) - (FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", "!", "~", 1)) + 1)), "")

    Second selection:

    =IFERROR(MID("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", "!", "~", 2)) + 1, FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", "!", "~", 3)) - (FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", "!", "~", 2)) + 1)), "")

    Third selection:

    =IFERROR(MID("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", "!", "~", 3)) + 1, FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", "!", "~", 4)) - (FIND("~", SUBSTITUTE("!" + SUBSTITUTE([Job Compliance & Training Requirements]@row, CHAR(10), "!") + "!", "!", "~", 3)) + 1)), "")

  • aaaah Ok, so each new columns that I want the extracted data in needs its own relevant formula?

  • Paul Newcome
    Paul Newcome Community Champion
    Answer ✓

    Yes, but you can just copy/paste then tweak those three numbers accordingly. You don't have to rewrite the entire thing or worry about different structures or anything like that.

  • One last question - IF in my multiple select dropdown column i have

    Row 1 - 1,2,3 and 5 of the available options selected

    Row 2 - 6,15,16 and 20 of the available options selected

    The formula is going to pull the data and insert into new columns so in column A we would have "1" in row 1 and "6" in row 2 because both in column A as row 1's first select is "1" and ditto of row 2 will should "6" in Column A

  • In the Job Compliance Cell of each row - the drop downs available are about 25 options so each row will have a different combination, so for example

    Row 1 with have DL, 4WD, First Aid selected

    Row 2 will have Electrical Ticket, HD DL, Working At heights

    Using the Parse formula am I going to end up with - (In Parse Helper 1 Column)

    Row 1 DL

    Row 2 Electrical Ticket

  • Paul Newcome
    Paul Newcome Community Champion

    Ok. And what is it you are wanting if that is not it?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!