Why can't I change the numbers in an excel list to be all the same so I can remove duplica

[Zotah]

Why can't I change the numbers in an excel list to be all the same so I can sort them and take out the duplicates.

I put in 2 sets of list from 2 different sources of phone numbers. I remove all hyphens spaces and parenthises just leaving the 9 number all tight together. Then I try to sort them and find that They wont sort. So I tried highlighting all the number together and change the size and fonds to all match each other and still they wont sort. I then change the format of all the numbers to General and try sorting and still they don't sort. My goal is to both sort and remove duplicates which I've done thousands of times before and these number won't. I'm mistified. Youtubes tell you how to sort and remove duplicates but nothing explains how to do it when the number come from 2 different sources. Also the top 2000 phone numbers in my list are lighter in color then the bottom 2000 phone numbers in the bottom half of the list telling me that the numbers are somehow formated differently. Also since they wont sort they wont remove duplicates which is exactly what I need to do. Remove Duplicates and make one clean list of phone numbers.

Any Ideas would be greatly appreciated.

Zotah

[MarvinP]

Hi Zotah,

I'd suggest you have spaces or "funny space looking" characters in front of those cells. We can help more if we could see a sample of what you have..

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Zotah]

I just downloaded a sample of the phone numbers. Please see if there is anyway to sort them or remove duplicates. Thanks

[Zotah]

I noticed that when I backspace the number that It fixes the problem and I can sort it. but to back space 2000 numbers would take forever. So I guess the new question would be How do I back space the blank space in front of the phone number without deleting the whole number. and I uploaded the sample excel sheet again just in case it didn't work the first time. Thanks

[Zotah]

Here is a small example of the exact list of 4000 I'm trying to sort and remove duplicates. I found that there is in fact a space in front of the half the number list. How to I remove that space and what is desensitize the data Mean? thanks

9499004980
9499160040
9499229961
9499297995
9499511789
9499513200
9499513530
9499515002
9499515974
9499517297
9499518088
9499518487
9499550555
9499550666
9499551667
9499552522
9499553388
9499634412
9499750944
*2076508016
*2076510579
*2092248359
*2092264993
*2092332200
*2092339070
*2092394683
*2092422365
*2092454140
*2092570270
*2092679163
*2092931475
*2092998591
*2093235603
*2093338488
*2093541040
*2093660746
*2093831673
*2093834234
*2093836751
*2093866777
*2094624510
*2094634435
*2094720573
*2094727378
*2094734045
*2094761111
*2094782766
*2094830891
*2095057568
*2095212727
*2095216566
*2095226962
*2095259855
*2095274220
*2095299244
*2095324053
*2095327539
*2095334560
*2095378989
*2095381699
*2095387758
*2095560955
*2095751040
*2095824061
*2095867556
*2095880823
*2095940119
*2095991650
*2096053568
*2096059725
*2096677012
*2097225302
*2097227783
*2097457664
*2097459222
*2097721021
*2098151867
*2098237828
*2098238169
*2098238698
*2098259267
*2098322997
*2098325110
*2098340345
*2098352720
*2098476425
*2098722021
*2099418631
*2099517147
*2099552244
*2099573361
*2099573995
*2099575385
*2133215873
*2133304724
*2133690291
*2133692544
*2133834534
*2133858431
*2133880367

[MarvinP]

Why don't you replace all the "*" characters with "" and see if that does what you expect.

Better than giving a list like above, it is more helpful if you attach a workbook, so we can see if there are other things keeping it from sorting.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[AlKey]

Hi Zotah,
Your data has some char(160) characters in front of the numbers. You need to remove them.
Enter formula in B1 and copy down. This will convert "numbers" entered as text to real numbers

=--TRIM(SUBSTITUTE(A1,CHAR(160)," "))

[Zotah]

Thanks, It works perfectly. Thanks so Much!