SOLVED

# Filtering Cells

Copper Contributor

# 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?

9 Replies
best response confirmed by VI_Migration (Silver Contributor)
Solution

# Re: Filtering Cells

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.

# Re: Filtering Cells

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

# Re: Filtering Cells

Did you paste just the values?

# Re: Filtering Cells

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

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

# Re: Filtering Cells

Of course, thanks!

# Re: Filtering Cells

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

# Re: Filtering Cells

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

# Re: Filtering Cells

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

# Re: Filtering Cells

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.

1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

# Re: Filtering Cells

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.