Filtering Cells

Copper Contributor

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?

9 Replies
best response confirmed by VI_Migration (Silver Contributor)


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.

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


Did you paste just the values?



@Hans Vogelaar 

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

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

@Sergei Baklan 

Of course, thanks!

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".

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.