Remove duplicates between text and number

[u/taylorgourmet]

Using the remove duplicates button did not work. I want to keep the text cells.

[u/excelevator]

Please be mindful of the submission guidelines

The post content must be a clear description of what you seek to achieve, not a vague comment based on the title as the description

Posts not following guidelines may be removed without notice.

[u/MayukhBhattacharya]

First select the columns, and then goto Data Tab --> Click Text To Columns --> First Step --> Delimited --> Next --> Second Step Select Only Next --> Third Step --> Column Data Format and Choose as Text --> Click Finish, now use the remove duplicates option from the data tab, it should work.

Or, using formula:

=UNIQUE(--A:.A)

Or to keep as text format:

=UNIQUE(A:.A)

[u/taylorgourmet → u/MayukhBhattacharya]

That changes them to number. I need them as text.

[u/MayukhBhattacharya → u/taylorgourmet]

You just need to format the cells as text at the last step, see below:

[u/kingk1teman → u/MayukhBhattacharya]

This is what you are looking for OP.

[u/taylorgourmet → u/MayukhBhattacharya]

solution verified thanks!

[u/GuerillaWarefare → u/taylorgourmet]

Do you want to keep original, or make all to text?

[u/chiibosoil]

It won't since number and text are not same.

There are number of methods...

1. Add helper column. Drag down.

=COUNTIF($A$2:A2,A2)

Then filter to show where this column = 2 and delete.

1. Text to Column -> Delimited -> Hit next, till step 3 of 3. Set column type to Text. Then remove duplicates.

etc.

[u/GuerillaWarefare]

You mention you want to keep the text cells… if there is a text and a similar number you want a list of the uniques while retaining text where it was present as text?

[u/PaulieThePolarBear]

Your example data showed exactly two instances of any value with one being text and one being numerical. With 100% certainty, is this true for all of your data? Said another way, the below scenarios are absolutely impossible in your data

• 3 or more instances of any value
• exactly 1 instance of any value with that value being text
• exactly 1 instance of any value with that value being numerical
• 2 or more instances of any value as text
• 2 or more instances of any value as numerical

[u/excelevator]

Sort the data to group, select and delete the numbers