I want to sort, ignoring all characters except numbers

[wojo]

I have a need to sort, a column, with cells that contain both
letters/characters and numbers. When the cell has a combination of
characters and numbers, I would like Excel to ignor any character that
is NOT a number and then sort the cells.

Example of data

6
[6
6]
[6]
7
12
14
14]

The above example 'shows' the resulting sort that I am looking for. I
need to keep the data displayed with the extra characters.

Any help out there?

 

[RagDyer]

You can use a "helper" column to extract the numbers with a text formula,
and then select the original *and* the "helper" column, and sort on the
"helper" column.
Then you can delete the helper.

This will extract the numbers from the type of data that you posted in your
example:

=--SUBSTITUTE(SUBSTITUTE(A1,"[",""),"]","")

If you example *didn't* show all the characters in your data, post back with
all possibilities, or at least a more extensive illustration.

--
HTH,

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

 

[wojo]

Here are more examples that would need sorting, properly.

6PHY
(6
6)
6CHE
6OT

Of course, there will be different numbers associated with each of the
'non-number' characters. I think this is just about it.

Thanks, Jo

You can use a "helper" column to extract the numbers with a text formula,
and then select the original *and* the "helper" column, and sort on the
"helper" column.
Then you can delete the helper.

This will extract the numbers from the type of data that you posted in your
example:

=--SUBSTITUTE(SUBSTITUTE(A1,"[",""),"]","")

If you example *didn't* show all the characters in your data, post back with
all possibilities, or at least a more extensive illustration.

--
HTH,

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

I have a need to sort, a column, with cells that contain both
letters/characters and numbers. When the cell has a combination of
characters and numbers, I would like Excel to ignor any character that
is NOT a number and then sort the cells.

Example of data

6
[6
6]
[6]
7
12
14
14]

The above example 'shows' the resulting sort that I am looking for. I
need to keep the data displayed with the extra characters.

Any help out there?

 

[RagDyeR]

Do you have square brackets *in addition* to parenthesis, or was the
original post a typo?

This is for what you posted as your latest example (no square brackets):

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")
,"PHY",""),"CHE",""),"OT","")
--

HTH,

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

Here are more examples that would need sorting, properly.

6PHY
(6
6)
6CHE
6OT

Of course, there will be different numbers associated with each of the
'non-number' characters. I think this is just about it.

Thanks, Jo

You can use a "helper" column to extract the numbers with a text formula,
and then select the original *and* the "helper" column, and sort on the
"helper" column.
Then you can delete the helper.

This will extract the numbers from the type of data that you posted in your
example:

=--SUBSTITUTE(SUBSTITUTE(A1,"[",""),"]","")

If you example *didn't* show all the characters in your data, post back with
all possibilities, or at least a more extensive illustration.

--
HTH,

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

I have a need to sort, a column, with cells that contain both
letters/characters and numbers. When the cell has a combination of
characters and numbers, I would like Excel to ignor any character that
is NOT a number and then sort the cells.

Example of data

6
[6
6]
[6]
7
12
14
14]

The above example 'shows' the resulting sort that I am looking for. I
need to keep the data displayed with the extra characters.

Any help out there?

 

[wojo]

Yes, I use both brackets and parenthesis. But, I am getting the idea.
When I have time to give it a try, I will respond here.

thanks in advance, JO

 

[Ron Rosenfeld]

I have a need to sort, a column, with cells that contain both
letters/characters and numbers. When the cell has a combination of
characters and numbers, I would like Excel to ignor any character that
is NOT a number and then sort the cells.

Example of data

6
[6
6]
[6]
7
12
14
14]

The above example 'shows' the resulting sort that I am looking for. I
need to keep the data displayed with the extra characters.

Any help out there?

I think you will require helper columns for both the numeric and non-numeric
portions of your data. Then you sort first on the numeric column and second on
the non-numeric column.

However, since the sorting you show above is not the way an Excel ASCII sort of
the non-numeric characters would normally come out, I had to make some changes.
These may or may not be satisfactory in all cases so you'll have to check.

In order to obtain the above sort order, I added a leading <space> to the
non-numeric portion if there was only a single non-numeric character.

I used to UDF's, one to extract the numeric portion, and the second to extract
the non-numeric portion. I entered these in two helper columns and then sorted
ascending first on the numeric, and then on the non-numeric.

To enter the UDF's, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer, then Insert/Module and paste the code
below into the window that opens.

Then in one column insert the formula:

=getnums(A1)

and in another column the formula:

=getstr(A1)

Change A1 to reflect the address of your first entry.

Copy/drag the formulas down as far as necessary.

Then Data/Sort
Ensure the column headers is appropriate for your data
Sort first by (whatever column has the extracted numbers)
and Then by (whatever column has the extracted text).

Finally, hide or delete the two helper columns.

==================================
Option Explicit

Function GetNums(str)
Dim N As Integer, i As String
i = ""
For N = 1 To Len(str)
If IsNumeric(Mid(str, N, 1)) Then
i = i & Mid(str, N, 1)
If Mid(str, N + 1, 1) = "." Then i = i & "."
End If
Next
If i = "" Then
GetNums = i
Exit Function
End If
GetNums = CDbl(i)
End Function

Function GetStr(str) As String
GetStr = ""

Dim N As Integer
For N = 1 To Len(str)
If Not (IsNumeric(Mid(str, N, 1))) Then GetStr = GetStr & Mid(str, N,
1)
Next
If Len(GetStr) = 1 Then GetStr = " " & GetStr
End Function
======================================



--ron

 

[RagDyer]

If we're thinking about going that far, how about one all encompassing
*array* formula, that will extract the numbers, *no-matter-what* amount or
type of Text is in the cell.

=--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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

I have a need to sort, a column, with cells that contain both
letters/characters and numbers. When the cell has a combination of
characters and numbers, I would like Excel to ignor any character that
is NOT a number and then sort the cells.

Example of data

6
[6
6]
[6]
7
12
14
14]

The above example 'shows' the resulting sort that I am looking for. I
need to keep the data displayed with the extra characters.

Any help out there?

I think you will require helper columns for both the numeric and non-numeric
portions of your data. Then you sort first on the numeric column and second on
the non-numeric column.

However, since the sorting you show above is not the way an Excel ASCII sort of
the non-numeric characters would normally come out, I had to make some changes.
These may or may not be satisfactory in all cases so you'll have to check.

In order to obtain the above sort order, I added a leading <space> to the
non-numeric portion if there was only a single non-numeric character.

I used to UDF's, one to extract the numeric portion, and the second to extract
the non-numeric portion. I entered these in two helper columns and then sorted
ascending first on the numeric, and then on the non-numeric.

To enter the UDF's, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the project explorer, then Insert/Module and paste the code
below into the window that opens.

Then in one column insert the formula:

=getnums(A1)

and in another column the formula:

=getstr(A1)

Change A1 to reflect the address of your first entry.

Copy/drag the formulas down as far as necessary.

Then Data/Sort
Ensure the column headers is appropriate for your data
Sort first by (whatever column has the extracted numbers)
and Then by (whatever column has the extracted text).

Finally, hide or delete the two helper columns.

==================================
Option Explicit

Function GetNums(str)
Dim N As Integer, i As String
i = ""
For N = 1 To Len(str)
If IsNumeric(Mid(str, N, 1)) Then
i = i & Mid(str, N, 1)
If Mid(str, N + 1, 1) = "." Then i = i & "."
End If
Next
If i = "" Then
GetNums = i
Exit Function
End If
GetNums = CDbl(i)
End Function

Function GetStr(str) As String
GetStr = ""

Dim N As Integer
For N = 1 To Len(str)
If Not (IsNumeric(Mid(str, N, 1))) Then GetStr = GetStr & Mid(str, N,
1)
Next
If Len(GetStr) = 1 Then GetStr = " " & GetStr
End Function
======================================



--ron

 

[Ron Rosenfeld]

If we're thinking about going that far, how about one all encompassing
*array* formula, that will extract the numbers, *no-matter-what* amount or
type of Text is in the cell.

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

I think your approach would be fine, but I believe you also need a second
column extracting only the text portion.

As written, and given a series of entries (as provided by the OP) such as:

6
(6
6)
[6
6]
[6]
6CHE
6OT
6PHY
7
12
14
14]
[6

Putting your formula in the next column and sorting on it results in:

6
(6
6)
[6
6]
[6]
6CHE
6OT
6PHY
[6
7
12
14
14]


Note the separation between the two identical entries: [6


--ron

 

[wojo]

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

 

[Ron Rosenfeld]

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

 

[Ragdyer]

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.

 

[wojo]

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

 

[RagDyeR]

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!
==============================================


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

 

[wojo]

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.

 

[wojo]

My other note is called

Display the TEXT of a comment in a new cell

Thanks again. This group is great!

Jo

 

[wojo]

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