T
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
*dl.dropboxusercontent.com/u/72261408/sort.png
right-click-> sort
right-click-> sort
T
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.
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
*textmechanic.com/Sort-Text-Lines.html
copy the content and click "Natural"
Simple
*i.imgur.com/zdUINXf.jpg
copy the content and click "Natural"
Simple
*i.imgur.com/zdUINXf.jpg
T
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
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.
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
*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.
T
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.
T
