I want to sort, ignoring all characters except numbers

Will it make a difference if the entry is 6 ] verses 6] or 6 phy
verses 6phy? (no space? or with a space?)

Thanks everyone, this is getting very interesting. I have never had a
class on Excel and I hunt and peck to get most things done. This help
is wonderful.

JO

On 6 Jul 2005 14:25:53 -0700, "wojo" <[email protected]> wrote:

>Will it make a difference if the entry is 6 ] verses 6] or 6 phy
>verses 6phy? (no space? or with a space?)
>
>Thanks everyone, this is getting very interesting. I have never had a
>class on Excel and I hunt and peck to get most things done. This help
>is wonderful.
>
>JO

A <space> is certainly considered a character. Whether it "makes a difference"
depends on how *you* define how *you* want things to sort.

It will make a difference in standard excel sorting. Just consult Excel HELP
for Sort, and study the "default sort order".


--ron

Would you comment on Ron's statement about the non-numeric characters not
sorting as a block with identical numbers unless they are also extracted.
First of all, do you have duplicates, and if so, is it objectionable if:
[6
6
6]
[6]
6CHE
6OT
6PHY
[6
was the final result of your sort?

As far as spaces are concerned, the spaces between the numbers and the
characters are eliminated.
And the second formula will eliminate any spaces between the characters.
However, spaces between the numbers will return errors.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"wojo" <[email protected]> wrote in message
news:[email protected]...
> Will it make a difference if the entry is 6 ] verses 6] or 6 phy
> verses 6phy? (no space? or with a space?)
>
> Thanks everyone, this is getting very interesting. I have never had a
> class on Excel and I hunt and peck to get most things done. This help
> is wonderful.
>
> JO
>

There won't be spaces between the numbers, however, different users
'may' put a space before or after the number. There will be duplicates
(which is normal and ok). Identical entries Do NOT need to be sorted
together.

The above sort result is exactly what I am looking for.

I haven't had time to digest the responses, so I am probably going to
ask the obvious...
1. Will I still need a "helper" column for sorting using the above
suggestion? I do NEED the 6's together, regardless of the extra
characters.
2. Where exactly to I put this formula? How do I get the formula and
the "6]" in the cell?

Thanks JO

Yes, you'll still need a "helper" column.

Say your column of data is Column A.
You can enter this *array* formula in Column B, or, you can insert a *new*
Column B, *temporarily*, if there's data in the present Column B.

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM
(--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1))))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

This is set for 100 rows.

After entering this in B1, you can click and drag down to copy, using the
"fill handle", the little black square in the lower right corner of the
selected cell (B1).
If you have contiguous data in Column A, you can *double click* on the "fill
handle". which will automatically copy the formula in B1 down Column B, as
far as there is data in Column A.

Now, just select both Column A and B, and sort them, using Column B as the
sort key.

You can then delete Column B if you wish.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"wojo" <[email protected]> wrote in message
news:[email protected]...
There won't be spaces between the numbers, however, different users
'may' put a space before or after the number. There will be duplicates
(which is normal and ok). Identical entries Do NOT need to be sorted
together.

The above sort result is exactly what I am looking for.

I haven't had time to digest the responses, so I am probably going to
ask the obvious...
1. Will I still need a "helper" column for sorting using the above
suggestion? I do NEED the 6's together, regardless of the extra
characters.
2. Where exactly to I put this formula? How do I get the formula and
the "6]" in the cell?

Thanks JO

Yeah! I got this to work, but I wasn't successful with the CSE type
entry. I simply used ENTER when I put the =ExtractNumbers(A1) in the
cell.

This works GREAT.... thanks to all that helped.

Jo

Please see my other (similar) problem with getting comments to print as
the value of a cell.

I just realized that I must have confused everyone. I didn't get to
try the answer that was given here. Instead, I found a note called
"Text to number - Hard" this one seemed to do the trick.

check it out

Jo

My other note is called

Display the TEXT of a comment in a new cell

Thanks again. This group is great!

Jo