Problems sorting with "/" in character strings

**CastorRJ1** · 11-03-2010, 05:00 PM

I am having problems sorting items within a spreadsheet that contain a forward slash "/". Below is an example of the data I'm trying to sort along with the error I'm receiving...

Data set:

1A/2/1
1A/2/2
1A/2/3
1A/2/10
1A/2/11
1A/2/12
1A/2/20
1A/2/21
1A/2/22
1A/2/30
1A/2/31
1A/2/32

The problem is that when I sort ascending, my data now appears like this:

1A/2/1
1A/2/10
1A/2/11
1A/2/12
1A/2/2
1A/2/20
1A/2/21
1A/2/22
1A/2/3
1A/2/30
1A/2/31
1A/2/32

How do I remedy this problem? Can I remedy this problem?

**NBVC** · 11-03-2010, 05:14 PM

Not sure what you are sorting by or if the 1A turns to 1B, etc....

But one way could be to first copy this column to a new column where there are at least 2 empty columns to the right of it, then select that column you pasted and go to Data|Text to Columns, select Delimited, click Next. Then select the Other checkbox and insert a forward slash in the adjacent box.

Click Finish.

You should have 3 columns with the separate parts... now you can sort the whole thing based on these 3 columns in any order you wish to get your desired output.

**CastorRJ1** · 11-04-2010, 10:47 AM

The problem I have is that I would like to keep all this data in it's own column rather than splitting it into three columns.

Eventually, the information will bump up to 1B/1/1, and so on...

**NBVC** · 11-04-2010, 10:54 AM

You will still have the original column.

I had said to first copy that column elsewhere in the sheet, and then split that new column up.. so that the original remains in tact....