Hi. The data in one field has multiple options for a user to select. When creating a filter for that field in shows the exact groupings from each entry (what each user selected) as opposed to a list of the choices to filter on. See screenshot. How do I fix this?
Use a Split Text column to convert your comma delimited text string into an array. Then change your filters to use that array.
3 Likes
I think I understand this. However, what do you mean by “array” in this context? thanks, joe.
Just change the filters to use the Split Text column (which is an array).
An array in Glide will appear as individual pills for each item in the table. Otherwise your comma delimited list is nothing more than a single piece of text. The comma on its own does not define each item separately.
An array is a type of data structure. If you want to get technical, here is a Wikipedia definition of it.
2 Likes
Hello again. Is this the correct solution? I appreciate your help.
To split a column containing comma-separated data into a single column array in Excel, you can use the “Text to Columns” feature. Here’s how to do it:
-
Select the column that contains the comma-separated data.
-
Go to the “Data” tab on the Excel ribbon.
-
In the “Data Tools” group, click on “Text to Columns”.
-
In the “Convert Text to Columns Wizard”, choose “Delimited” and click “Next”.
-
In the “Delimiters” section, check the box next to “Comma” and uncheck any other delimiters if they are selected. Click “Next”.
-
In the “Column data format” section, choose “General” or the appropriate format for your data. Click “Finish”.
After following these steps, the comma-separated data will be split into separate columns. To convert the data into a single column array:
-
Insert a new column next to the split data.
-
In the first cell of the new column, enter the following formula (assuming the split data starts in column A):
=TRANSPOSE(A1:A10)
Replace “A1:A10” with the actual range of cells containing the split data.
-
Press “Ctrl+Shift+Enter” to enter the formula as an array formula. Excel will automatically add curly braces around the formula: {=TRANSPOSE(A1:A10)}
-
The data will now appear as a single column array in the new column.
Note: If you have data in multiple rows, you may need to modify the range in the TRANSPOSE formula to include all the relevant cells.
Alternatively, if you prefer a non-formula approach, you can copy the split data, select a cell in the new column, right-click, and choose “Paste Special”. Then, select “Transpose” and click “OK”. This will paste the data as a single column array without using a formula.
1 Like
Thank you. That did the trick. However, my text string has phrases separated by commas, such as “access to expert providers” and “Copay Accumulators” and with split text, each word in the group is split so the filter is on each word, not the phrases. Any suggestions?
The default ‘split by’ (delimiter) value in a Split Text column is a comma. Did you change it to something else?
Since your delimiter appears to be a comma followed by a space, I recommend your ‘split by’ value also be a comma and a space. Preferably you would eliminate any spaces before and after the comma, but that all depends on how your comma delimited text gets filled in the first place.
1 Like
Got it. It worked. Thanks so much!
1 Like