Forum Discussion
Solved
Filtering Cells
I have an Excel column with with various amounts of comma separated values, how can I remove all values that exceed 6, so that no more than 6 values remain in each cell?
Let's say your values are in F2 and down.
In G2, enter the formula
=TRIM(LEFT(SUBSTITUTE(G2, ",", REPT(" ", 255), 6), 255))=TRIM(LEFT(SUBSTITUTE(F2, ",", REPT(" ", 255), 6), 255))
Fill down.
If you want to get rid of the original values, copy column G and paste it as Values over column F. You can then remove column G.
why not share a file with dummy data and show your expected result?
music_shop_yelp_test.csv (original)
music_shop_yelp_filter.csv (stripped brackets)
I used Google Sheets.
Thanks. The strings are far too long for the formula that I posted earlier.
When I open the second file (the filtered one) in Excel, the comma-separated values are in column B.
The following formula in an empty column in row 2 will return at most 6 comma-separated values:
=IFERROR(LEFT(B2, FIND("|", SUBSTITUTE( B2, ",", "|", 7))-1), B2&"")
This can be filled down.
See the attached workbook.
