theterminator
Wise Old Owl
I have a column with fields like
'v1,v2,v3,v4...etc'
Its not sorted,how do I sort it?
'v1,v2,v3,v4...etc'
Its not sorted,how do I sort it?
Sorting alphanumeric cell in excel 2007
- Thread starter theterminator
- Start date
Nerevarine
Incarnate
*dl.dropboxusercontent.com/u/72261408/sort.png
right-click-> sort
right-click-> sort
OP
theterminator
Wise Old Owl
It ain't that simple ..
That will sort first alphabets and then numbers from left to right.
v1,
v10,
v100,
v11,
v110,
v111
& so on.
I want it to be
v1,
v2,
v3,
v4
...
v97,
v98,
v99,
v100...& so on.
Nerevarine
Incarnate
Not a solution but you could use this :
click on a cell adjacent to the particular cell
use this function
=RIGHT(A1,LEN(A1)-1)
this is assuming A1 cell has the particular value you need to be sorted
now drag this generated cell down as long as your list is..
select everything, click sort -> custom sort
in the sort by option, select column 2, select A-Z and press OK
In the new dialog box, select sort anything that looks like a number as number
press ok
after you are done, delete the second list
*www.dropbox.com/s/0dmqj4tlstyl9al/Screenshot%202015-08-02%2019.51.31.png?dl=1
click on a cell adjacent to the particular cell
use this function
=RIGHT(A1,LEN(A1)-1)
this is assuming A1 cell has the particular value you need to be sorted
now drag this generated cell down as long as your list is..
select everything, click sort -> custom sort
in the sort by option, select column 2, select A-Z and press OK
In the new dialog box, select sort anything that looks like a number as number
press ok
after you are done, delete the second list
*www.dropbox.com/s/0dmqj4tlstyl9al/Screenshot%202015-08-02%2019.51.31.png?dl=1
OP
theterminator
Wise Old Owl
I'm trying to upload the excel file through PC but its saying Upload of file failed ... tried in pdf format also but still error persists.
anyways, here's a google drive link with write privileges to the file
*drive.google.com/file/d/0BwuIJyrcoykBQzVqMEdLRE1xS3c/view?usp=sharing
anyways, here's a google drive link with write privileges to the file
*drive.google.com/file/d/0BwuIJyrcoykBQzVqMEdLRE1xS3c/view?usp=sharing
Flash
Lost in speed
I cant' access the drive from office, but you can try below.
1. Use MID function (Cell value, substring position from the cell, substring length from cell. Ex: MID("TEXT",2,3) will extract EXT & MID("TEXT",1,2) will extract TE) to segregate the numbers alone in 2nd column and drag for all "A" cells.
2. Copy the contents from B-cell and paste as "values" in C-cell. Make sure you convert it as Number.
3. Once they are available in number, Use "Sort & Filter > Smallest to Largest and then, Expand the Selection". All cells will be sorted numerically.
4. Now delete B & C cell.
1. Use MID function (Cell value, substring position from the cell, substring length from cell. Ex: MID("TEXT",2,3) will extract EXT & MID("TEXT",1,2) will extract TE) to segregate the numbers alone in 2nd column and drag for all "A" cells.
| Value | =MID(A1,2,3) |
| v10 | 10 |
| v1 | 1 |
| v111 | 111 |
| v11 | 11 |
| v110 | 110 |
| v100 | 100 |
2. Copy the contents from B-cell and paste as "values" in C-cell. Make sure you convert it as Number.
3. Once they are available in number, Use "Sort & Filter > Smallest to Largest and then, Expand the Selection". All cells will be sorted numerically.
4. Now delete B & C cell.
kartikoli
Busy!!
I couldn't do it but we have a quick fix
go to *textmechanic.com/Sort-Text-Lines.html
in excel file move Card no. column to first column, Now copy everything (Not the highlighted line) *i.imgur.com/PwTJ3Yr.jpg
paste in techmachanic website click natural and copy everything back to excel file.
Done
Nerevarine
Incarnate
*dl.dropboxusercontent.com/u/72261408/SampleEdited.xlsx
Here you go, Is this what you needed ? I left the new column with the formula i mentioned previously.. just delete it after you have understood what it did.
Here you go, Is this what you needed ? I left the new column with the formula i mentioned previously.. just delete it after you have understood what it did.
OP
theterminator
Wise Old Owl
The MID function did the trick. Thanks Flash & others for your help .
.
The MID function did the trick. Thanks Flash & others for your help
Well...that took some months.
OP
