Joining a text field with a multi-select field

Whelandon

I have a sheet where a unique ID field is created by joining two columns. Originally they were both text columns but one of the columns changed to a multi-select column. The issue that we're having is that when more than one option is selected in the multi-select column, it's causing the resulting text in the unique ID field to not be recognizable in the index/match formula we're using on a different sheet. We tested it by pasting plain text over the cell in question and the other sheet was able to pull in the information correctly. Also, all of the rows where only one option is selected in the multi-select weren't causing any issues. Has anyone else experienced this? Thanks in advance.

Paul Newcome

When you are looking at a multi-select column, it uses a line break as a delimiter. If you do not have text wrapping enabled, it will just look like a string (but actually be multiple lines). If you are searching for a string that has line breaks in it, but the range you are searching across does not, then you will not get a match.

Here's something you can try. Use the SUBSTITUTE function to remove the line breaks. So if your current formula that combines the two cells looks something like this...

=[Unique ID]@row + [Multi-Select Column]@row

You can try something along the lines of...

=[unique ID]@row + SUBSTITUTE([Multi-Select Column]@row, CHAR(10), "")

This will remove the line breaks from the multi-select column so that it truly is a single text string.

Shelby

@Paul Newcome

I used your solution above and it worked. However, when I select multiple values in the multi-select column the text string returns to the cell as one string with no spaces...see screenshot below - "Job Name - Cost Event Type(s)" column.

I know how to concatenate or add delimiters to return the text values to one cell but I don't know how to do that when the SUBSTITUTE formula is involved.

Any help you could provide would be appreciated :)

Paul Newcome

@Shelby Can you provide the exact formula you are using as well as an example of exactly how you want the data to be displayed?

Shelby

@Paul Newcome

I think I've figured it out...

I've changed some minor things since the last screenshot but here's the current formula that is in the "Cost Event Type(s) Description" column:

=IF([Actual or Potential Cost?]@row = "Potential", "Potential - ", "") + SUBSTITUTE([Cost Event Type(s)]@row, CHAR(11), "")

Paul Newcome

@Shelby Glad you were able to get it working. Out of curiosity... What is the purpose of this portion?

SUBSTITUTE([Cost Event Type(s)]@row, CHAR(11), "")

Shelby

@Paul Newcome I wanted the "Actual or Potential Cost?" (only if it says "Potential") and the "Cost Event Type(s)" values combined into one cell and there be line breaks between the characters.

Here's what I've ended up with that I think represents the values the best:

=(IF([Actual or Potential Cost?]@row = "Potential", "POTENTIAL", "") + CHAR(10) + SUBSTITUTE([Cost Event Type(s)]@row, CHAR(11), ""))

Paul Newcome

Ok. But what I am wondering is what the CHAR(11) represents and why you need to remove it.

Shelby

Hmmm 🤔

Not sure actually...I removed the SUBSTITUTE and CHAR portion of the formula and added the @row reference and the data is still represented the same.

=(IF([Actual or Potential Cost?]@row = "Potential", "POTENTIAL", "") + [Cost Event Type(s)]@row)

Paul Newcome

That's what I was thinking would be the case but wasn't sure. Thanks for testing it.