I would like to sort my data by the HATS number (located in column A) but when instead I am being returned two sets of results. cells 1 through 5499 get sorted(from lowest to higher) then cells 5501 through 6152 get sorted the same way.
How can I have all the data sorted together
Looks like the data has been input in 2 different formats - rows 2 to 5498 show (for example) 999-04-0558 but the number is really 999040558. The second column below is with no formatting.
| 999-04-0533 | 999040533 | Barrock |
| 999-04-0558 | 999040558 | Coleman |
| 999-04-0647 | 999040647 | Davis |
But 5499 onwards it is input with the dashes
| 030-82-4918 | 030-82-4918 | Diallo |
| 042-11-6469 | 042-11-6469 | Chan |
| 042-76-9968 | 042-76-9968 | Fofana |
You could add a formula to make the numbers into the dash format - the below seems to work (added in column C).
=IFERROR(IF(SEARCH("-",A2)=4,A2,""),LEFT(REPT("0",9-LEN(B2))&B2,3)&"-"&MID(REPT("0",9-LEN(B2))&B2,3,2)&"-"&RIGHT(REPT("0",9-LEN(B2))&B2,4))
I then copied column C, pastespecial> values and sorted (yellow rows are the 5499 onwards rows), seems to work
fyi - I have saved the file as xlsb to get around the 1meg file upload limit - there is no vba in it
Hi Damali
The quick way will be select Cell A5499 downwards, Ctrl H, replace "-" with blank and you are able to sort it
Cheers !
