Sorting

[By_Sal]

Hello everyone, this text is translated from Italian by Google.

I would like this type of sorting

my column is of this type
Program 1
Program 2
Program 3
Program 9
Program 3
Program 15
Program 14
Program 3
Program 7
Program 6


and I would get this result
Program 1
Program 2
Program 3
Program 3
Program 3
Program 6
Program 7
Program 9
Program 14
Program 15

instead that this
Program 1
Program 14
Program 15
Program 2
Program 3
Program 3
Program 3
Program 6
Program 7
Program 9

how to use Excel to sort?

Thanks By Sal

 

[NARAYANK991]

Hi Sal ,

Can you say how long your list is i.e. how many items are there ?

Anyway , if you can use a helper column , then it is easy ; assuming that your list of items is in column A , from A2 downwards , in B2 , put in the following formula :

=SUBSTITUTE(A2,"Program ","")+0

Copy down.

Now give your sort range including column B also , and sort on column B.

Narayan

 

[sgmpatnaik]

@Narayan

Hello i think the op is try to sort the words using with 9th letter so i think when it is converted in to number format
then the result will go fail i think

without converting the number format it will possible for sort may be

=SUBSTITUTE(A2,"Program ","")

and sort,
when we sort then we get a warning message that is

Sort Warning
The Following sort key may not sort as expected because
it contains some numbers formatted as Text:
What would you like to do ?
1. Sort any thing that looks like a number , as a number
2. Sort Numbers and Numbers stored as text seperatly


and select the second option then the result come as per OP required

if any wrong please suggest

Thanks

 

[NARAYANK991]

Hi Patnaik ,

You are right.

Narayan

 

[By_Sal]

Thanks Patnaik but does not exit the notice that you have specified how do I make it appear?

thanks Hello

 

[sgmpatnaik]

Hi

in B2 write the formula
=SUBSTITUTE(A2,"Program ","")

drag down to ur desire range. say your range is A2:A100 write the formula and drag
Now select the range A1:B100 now sort the data with column B

hope it solve ur problem other wise please upload a sample file

 

[Smallman]

This forumula needs to be adjusted slightly

Program 1 is text, as such the formula above will produce a text result. This needs to be turned into a number to get a different result when Sorting.

=VALUE(SUBSTITUTE(A2,"Program ",""))

This will produce the same result as Narayan solution. But I see it this way and I could be wrong. Would like to see a file.

Should get the job done. A file is attached showing both results from a respective Sort.

Take care

Smallman