Filtering Cells

[Nostromo]

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?

[#2 · Accepted solution]

@Nostromo 

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.

[#3]

Thanks for the reply, however when I pasted over, the cells went blank. I'm using Excel online.

[#4]

@Nostromo 

Did you paste just the values?

[#5]

@HansVogelaar 

That's misprint in the formula. If value is in F2, it shall be

=TRIM(LEFT(SUBSTITUTE( F2, ",", REPT(" ", 255), 6), 255))

[#6]

@SergeiBaklan 

Of course, thanks!

[#7]

I cut everything back to one cell - A1, pasted the formula in B1 & the cell populated with data which I pasted as values back into A1, it worked! I then opened the original file & tried it with a full column, worked!

Thanks for your patience, I've been struggling with this for months. My spreadsheet knowledge is basic & I have no Idea how these formulas work, anyway my motto is "you can't know less by learning more".

[#8]

why not share a file with dummy data and show your expected result?

[#9]

music_shop_yelp_test.csv (original)
music_shop_yelp_filter.csv (stripped brackets)

I used Google Sheets.

[#10]

@Nostromo 

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.