Excel Sort values with / sign within it

[Jyotirmaya]

I have a list in Excel for example 1 2 3 .... 99, 100 then after 100 I have values like 100/1 100/2...100/10..100/20

Excel sort formula is working up to 100 but after that the sorting is like 100/1 100/10 100/11... 100/2 100/21

I want the / (divide) sign values sorting should be like 100/1 100/2 100/3 & so on

 

[yky]

Could you post a sample of real data?

 

[Special-K99]

When you post this file includes examples of 1, 2, 3, 100/1, 100/2, 100/3, 100/10, 100/20 and...
1000/1, 1000/10, 1000/20 if they could exist so we can see where they should get sorted amongst your other data

 

[Jyotirmaya]

When you post this file includes examples of 1, 2, 3, 100/1, 100/2, 100/3, 100/10, 100/20 and...
1000/1, 1000/10, 1000/20 if they could exist so we can see where they should get sorted amongst your other data

 

[yky]

The only way I know is use a helper column. Find an empty column and enter the formula "=RIGHT(A1,LEN(A1)-FIND("/",A1))" and copy the formula down. It will give you the part after "/". You then sort the main column and this helper column. However, this only works if the first parts are same, in this case, 102. If not, you need two helper columns. Maybe somebody has a better idea.

 

[shg]

You can do Data > Text to columns to split the data (retaining the original, if you like), then sort on the results of that:

	A​	B​	C​
2​	100/2	100​	2​
3​	100/22	100​	22​
4​	100/1	100​	1​

 

[Jyotirmaya]

You can do Data > Text to columns to split the data (retaining the original, if you like), then sort on the results of that:

	A	B	C
2	100/2	100	2
3	100/22	100	22
4	100/1	100	1

<tbody>
</tbody>

Any VBA macro is possible ? Because if I will do like this then its a big excel file & have to do for so many work sheet,

I have the values in A column, with the macro text to columns to split data with / symbol, the data will be split on B & C, then in column D all data starting from A column 1..2..3 will be pasted & with sorted and concatenated figures (=CONCATENATE(B115,"/",C115) or if any better option will be pasted by considering Column E TO AB. Column E TO AB should be changed accordingly when the data will be sorted on column D.

I want this to be like this

 

[yky]

Any VBA macro is possible ? Because if I will do like this then its a big excel file & have to do for so many work sheet,

How many sheets are there? If less than 10, I would think copy & paste might be faster.

I have the values in A column, with the macro text to columns to split data with / symbol, the data will be split on B & C, then in column D all data starting from A column 1..2..3 will be pasted & with sorted and concatenated figures (=CONCATENATE(B115,"/",C115)

If you include Column A in sorting, you don't need Column D.

 

[Jyotirmaya]

How many sheets are there? If less than 10, I would think copy & paste might be faster.

If you include Column A in sorting, you don't need Column D.

There are more than 100+ sheets, any macro would be better

 

[yky]

OK. The macro should be easy to write but I'll need a representative copy of the worksheet.