Excel help - filtering

[audi321]

Hi all, does anyone know how to filter in excel so the following example would appear in order from the numbers.

i.e.

ABCDE-1
ABCDE-2
ABCDE-100
ATFSG-2000

etc.

I need to filter by the 7th digit onwards.

Thanks for any help.

[NormalWisdom]

I presume you mean Sort?

If so, extract the las "n" digits into an adjacent sell (=right(E1, 3), takes the last 3 cells in E1 and places them int your working cell

Select the whole lot and then sort on the extracted data

[NormalWisdom]

Except that will not work if the content is of varying length - I'll think again!

[louiebaby]

NormalWisdom said:

I presume you mean Sort?

If so, extract the las "n" digits into an adjacent sell (=right(E1, 3), takes the last 3 cells in E1 and places them int your working cell

Select the whole lot and then sort on the extracted data

This is good advice.

For the 7th digit on, you want =MID(E1,7,999) though, which might be more useful in this respect.

[kennydies]

You can take it from the right when you search for the "-" in the string

[Mammasaid]

It the 1st 6 characters will always be 5 letters + '-' , then copy column to a new one, then do Text to Columns to parse out the letters and the numbers, then sort on the numbers expanding the sort to include your original column.

[kennydies]

So if you copy your example into A2 you can use this formula:
=RIGHT(A2,LEN(A2)-SEARCH("-",A2,1))

[NormalWisdom]

However, =Replace (E6,1,6,"")


Where E6 is the original content, 1 is the start point and 6 is the number of digits in - Then sort as above

[kennydies]

or to combine both our solutions and make it dynamic:
=REPLACE(A2,1,SEARCH("-",A2,1),"")

[NormalWisdom]

louiebaby said:

For the 7th digit on, you want =MID(E1,7,999) though, which might be more useful in this respect.

This works too

[NormalWisdom]

kennydies said:

or to combine both our solutions and make it dynamic:
=REPLACE(A2,1,SEARCH("-",A2,1),"")

Cute

[audi321]

Wow thanks all.

I take it there's no option other than to create another column then? I was trying to avoid this if possible?

[kennydies]

No, you could use the "Contains" function in the filter and manually type the number but I am thinking that is not what you want

[audi321]

Ok another related question then, if the first lot of characters were identical, ie.

ABC-123
ABC-1234
ABC-12345
ABC-543

Is there a way to put them in numerical order without creating another column? As when I search it orders by the first number rather than in the actual order which should be:

ABC-123
ABC-543
ABC-1234
ABC-12345

[Steven_RW]

What is the reason for not wanting to create the new column that will be used for the sort?

You could create it and then move it far right out the way, or create it and hide it?

[audi321]

It's part of a template which gets created automatically and I'm not the owner so to mess around creating new columns will be frowned upon lol.

[louiebaby]

audi321 said:

Ok another related question then, if the first lot of characters were identical, ie.

ABC-123
ABC-1234
ABC-12345
ABC-543

Is there a way to put them in numerical order without creating another column? As when I search it orders by the first number rather than in the actual order which should be:

ABC-123
ABC-543
ABC-1234
ABC-12345

None that I can think of that operate without using VBA.

Also, if you go down the MID or RIGHT methods described above, you might want to put in a *1 in the formulae too, so that the outcome is turned into a number, not just kept as text.