Parse-ing

Susan van Niekerk

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

Paul Newcome

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)), "")

Paul Newcome

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.

Paul Newcome

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.

Susan van Niekerk

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

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)), "")

Susan van Niekerk

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

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)), "")

Susan van Niekerk

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

Paul Newcome

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.

Susan van Niekerk

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

Paul Newcome

I'm not sure I follow.

Susan van Niekerk

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

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