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?

Brendan wrote:

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.

--

Dave Peterson

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.

"Dave Peterson" wrote:

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?

Brendan wrote:

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.

--

Dave Peterson

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)



Brendan wrote:

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.

"Dave Peterson" wrote:

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?

Brendan wrote:

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.

--

Dave Peterson


--

Dave Peterson

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.

"Dave Peterson" wrote:

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)



Brendan wrote:

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.

"Dave Peterson" wrote:

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?

Brendan wrote:

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.

--

Dave Peterson


--

Dave Peterson

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" wrote in message
...
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.

"Dave Peterson" wrote:

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)



Brendan wrote:

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.

"Dave Peterson" wrote:

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?

Brendan wrote:

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.

--

Dave Peterson


--

Dave Peterson

You can also use the second suggestion to retrieve the rest of the characters.

Did it not work for you?

Brendan wrote:

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.

"Dave Peterson" wrote:

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)



Brendan wrote:

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.

"Dave Peterson" wrote:

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?

Brendan wrote:

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.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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.

"MartinW" wrote:

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" wrote in message
...
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.

"Dave Peterson" wrote:

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)



Brendan wrote:

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.

"Dave Peterson" wrote:

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?

Brendan wrote:

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.

--

Dave Peterson


--

Dave Peterson

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 specialvalues 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" wrote in message
...
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.

"MartinW" wrote:

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" wrote in message
...
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.

"Dave Peterson" wrote:

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)



Brendan wrote:

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.

"Dave Peterson" wrote:

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?

Brendan wrote:

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.

--

Dave Peterson


--

Dave Peterson