Can't figure out a certain column sort

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following in a colum:

256
128
148(a)
299
301

The I would like the order to end up with a numeric sort but everytime I try
it, the 148(a) ends up at the bottom. Is there a way to get the column to
sort with the 148(a) where it should be... after the 128 and before the 256?
I can't remove the parenthesis as it is part of the data.
 
Can you insert a couple of helper cells and put formulas in them to extract the
numbers?

Then sort the entire range based on those two columns?

If you can, then is that the only kind of non-numeric in your data?
 
I could possbily do a "text to column" but there are some codes that are like
245(a)(1) and 273a(a)(1). All of the problem data either ends with the
parenthesis or a letter.
 
If you don't care about what the extra characters are, you can use an array
formula like:

=IF(ISNUMBER(A1),A1,
--LEFT(A1,MATCH(FALSE,ISNUMBER(-MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Or with that formula in B1, you could use this in C1 to get that extra info:
=MID(A1,LEN(B1)+1,255)
 
I do care what the extra characters are because in the long run, I need to
wrap them back up as they originally were. I just need to strip them for the
initial sorting purposes.
 
Hi Brendan,

If all of your data is like your example with 3 numbers first, then
a helper column with =LEFT(A1,3) and dragged down to
the end of your data is all that is needed, simply sort on the helper
column and delete it when finished.

If that is not the case then post back showing all of the variations
in your data.

HTH
Martin


Brendan said:
I do care what the extra characters are because in the long run, I need to
wrap them back up as they originally were. I just need to strip them for
the
initial sorting purposes.
Click to expand...
 
You can also use the second suggestion to retrieve the rest of the characters.

Did it not work for you?
I do care what the extra characters are because in the long run, I need to
wrap them back up as they originally were. I just need to strip them for the
initial sorting purposes.
Click to expand...
 
There are all sorts of variations going up to about 10 characters:

256
128
148(a)
299
301
12025(a)(1)
4600
12031(d)(1)(a)
5200c
....etc.
 
Hi Brendan,

This should strip out the majority of your numbers,
=IF(ISERROR(FIND("(",A1)),A1,LEFT(A1,FIND("(",A1)-1))
Put it in B1 and drag down.

It doesn't handle the 5200c though

Perhaps do a copy >paste special>values on this helper column
and then run a new helper column to take out the c's, OR you
may even be able to to do a find and replace to replace c
with nothing. Even if you have to do a find and replace
for every letter it won't take too long.

HTH
Martin


Brendan said:
There are all sorts of variations going up to about 10 characters:

256
128
148(a)
299
301
12025(a)(1)
4600
12031(d)(1)(a)
5200c
...etc.
Click to expand...
 
Back
Top