How to column by numerical value?

[Helios9]

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.

 

[Yogi Anand]

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]

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