How to column by numerical value?

Helios9

Board Regular
Joined
Oct 15, 2006
Messages
127
I have a single column, each cell beginning with 'RG-' and followed by a three or four digit number followed by '.jpg'. For example:

RG-9002.jpg
RG-2005.jpg
RG-201.jpg
RG-908.jpg
RG-550.jpg
RG-2009.jpg
RG-2548.jpg

I want to sort the column low to high, based on numerical value. The sort ascending function places the above cells into the order shown below.

RG-2005.jpg
RG-2009.jpg
RG-201.jpg
RG-2548.jpg
RG-550.jpg
RG-9002.jpg
RG-908.jpg

I need it to sort in numerical order, like this:

RG-201.jpg
RG-550.jpg
RG-908.jpg
RG-2005.jpg
RG-2009.jpg
RG-2548.jpg
RG-9002.jpg

Any help is much appreciated.
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Helios9:

Welcome to MrExcel Board!

Here is one way.

1) Let us say your FileNames begin in cell B2 and down. Cell B1 has the lable FileName

2) Use Column C as Intermediary column. C1 has lable Intermediary and in cell C2 write the formula ...
=TEXT(REPLACE(MID(B2,4,8),FIND(".",MID(B2,4,8)),4,""),"0000")
and copy it down as far as necessary

3) Now sort columns B:C by column C in Ascending Order. You may now optionally delete the Intermediary column

That should do it.
 

Helios9

Board Regular
Joined
Oct 15, 2006
Messages
127
Thank you Yogi. I appreciate the very fast response.

I did just discover another way also. I deleted the prefix (RG-) and suffix (.jpg) with the find-and-replace tool. The sort ascending tool then worked as desired and placed the numbers in true numerically ascending order. I then concatenated the suffix back into the column.

Best regards, Dean :LOL:
 

Watch MrExcel Video

Forum statistics

Threads
1,112,885
Messages
5,543,028
Members
410,583
Latest member
gazz57
Top