|The problem is that your data is not consistant. If you had a column that contained either a 10 digit number or an 8 digit number, the process could be automated fairly easily.|
However, since you have "random" formats of data like these, there is going to have to be some manual intervention:
Here's the best I can offer:
With your list of data starting in A1...
Enter this in B1:
Enter this in C1:
Select B1 and C1 and drag these formulas down to the bottom of your list.
In Column B you should have:
1 - All of the 10 digit numbers
2 - All of the random formats mentioned above
3 - A bunch of blank cells
In Column C you should have:
1 - All of the 8 digit numbers
2 - A bunch of blank cells
Next, select Columns B and C and do an Edit...Copy...Edit...Paste Special - Values
This will eliminate the formulas and leave just the numbers.
Next, Select Column B and Sort Ascending (only sort on Column B)
Next, Select Column C and Sort Ascending (only sort on Column C)
This should bring all of your numbers to the top of the columns.
Now, way at the bottom of Column B you should have all of your random formats.
Use Data...Text To Columns to separate these into separate Columns.
Now repeat the above steps on that data and move stuff around until you have what you are looking for.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.