Is there a formula to trim a cell of all duplicate values and zeroes?

Question

For example, I have Cell A1 that has "4, 0, 0, 0, 4" and I want to know if I can input a formula in B to spit out just "4".

+==================================================+===================================+
|                    Col A  - Source               |  Col B  - Result on referencing Col A
+==================================================+===================================+
| 4, 0, 0, 0, 4                                    | 4                                 |
+--------------------------------------------------+-----------------------------------+
| 268, 0, 0, 0, 268                                | 268                               |
+--------------------------------------------------+-----------------------------------+
| 282, 283, 0, 0, 0, 0                             | 282, 283                          |
+--------------------------------------------------+-----------------------------------+
| 0, 0, 0, 0, 0                                    |                                   |
+--------------------------------------------------+-----------------------------------+
| 0, 0, 4324, 4325, 4326, 0, 4324                  | 4324, 4325, 4326                  |
+--------------------------------------------------+-----------------------------------+
| 450, 1307, 1308, 1309, 1310, 1321, 0, 0, 0, 1309 | 450, 1307, 1308, 1309, 1310, 1321 |
+--------------------------------------------------+-----------------------------------+

http://stackoverflow.com/questions/3017852/vba-get-unique-values-from-array — findwindow, Jun 16 '16 at 23:28
Getting rid of the 0 is easy using substitute, the problem is removing the duplicates. It will take vba. — Scott Craner, Jun 16 '16 at 23:28

score 0 · Answer 1 · answered Apr 07 '17 at 15:01

You can delete the zeros with this formula =SUBSTITUTE(A2&",","0,","") but you'll need use some VBA or the remove duplicates button to remove duplicates values.

The alternative is to apply "Data" => "Text in column" to the column A and then extract the max value. This will ignore zeros and will extract one number for each rows. But will not extract multiple values like 282, 283.

Is there a formula to trim a cell of all duplicate values and zeroes?

1 Answers1