SOLVED

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

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

@Nostromo 

Did you paste just the values?

 

HansVogelaar_0-1682773578395.png

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

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

1 best response

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
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.

View solution in original post