# How to column by numerical value?

#### Helios9

##### Board Regular
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.

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### Yogi Anand

##### MrExcel MVP
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
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

Replies
4
Views
104
Replies
2
Views
276
Replies
11
Views
189
Replies
3
Views
52
Replies
3
Views
105