S
stevebicks
Is there a way in excel to ignore spaces or commas etc when sorting a-z
cheers
Stev
cheers
Stev
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Dave Peterson
You mean spaces embedded in values?
I'd use a helper column full of formulas like:
=SUBSTITUTE(SUBSTITUTE(A1," ",""),",","")
(It removes the spaces and the commas.)
Then sort by that column instead of the original.
I'd use a helper column full of formulas like:
=SUBSTITUTE(SUBSTITUTE(A1," ",""),",","")
(It removes the spaces and the commas.)
Then sort by that column instead of the original.
D
Dave Peterson
Just another way:
Copy that original column and paste special|values into a helper column.
Select that helper column and
Edit|replace
(spacebar)
with
(leave blank)
Replace all
and once more
, (comma)
with
leave blank
Replace all
And sort on this column.
Copy that original column and paste special|values into a helper column.
Select that helper column and
Edit|replace
(spacebar)
with
(leave blank)
Replace all
and once more
, (comma)
with
leave blank
Replace all
And sort on this column.
S
stevebicks
the specific problem i have is an excel sheet which is a songlist, th
list has columns as song title, artist, disc number,track number
i want to sort the list A-Z by song title then artist, but ignoring an
spaces, commas etc.
cheers
Stev
list has columns as song title, artist, disc number,track number
i want to sort the list A-Z by song title then artist, but ignoring an
spaces, commas etc.
cheers
Stev
S
stevebicks
lol being a newbie i just worked out how to use edit/replace as yo
suggested, which has worked fine but isn't there an easier way?
thanks again
Stev
suggested, which has worked fine but isn't there an easier way?
thanks again
Stev
D
Dave Peterson
If you want excel to ignore something it doesn't want to ignore, you have to do
something to the data (or a copy of the data).
Maybe the =substitute() suggestion would be easier to copy down each time you
add a new row.
something to the data (or a copy of the data).
Maybe the =substitute() suggestion would be easier to copy down each time you
add a new row.
D
David McRitchie
Hi Steve,
Here is a user defined function that will do what you asked for
you will need to install the user defined function (UDF) and
use a helper column.
=AlphaWithDigits(A10)
=personal.xls!AlphaWithDigits(A10)
You asked to remove all spaces.
Function AlphaWithDigits(ByVal s As String) As String
'D.McRitchie, .excel, 2004-02-15 modified string for LIKE
'otherwise is same as a posting
'by Harlan Grove, worksheet.functions, 2003-10-20
Dim i As Long, n As Long
n = Len(s)
For i = 1 To n
If Not Mid(s, i, 1) Like "[0-9 A-Z a-z]" Then Mid(s, i, 1) = " "
Next i
AlphaWithDigits = Application.WorksheetFunction.Substitute(s, " ", "")
End Function
If you decided to retain a representative existing space between words,
you would have to make a few changes.
=AlphaWithDigitsSpaces(A10)
=personal.xls!AlphaWithDigitsSpaces(A10)
Function AlphaWithDigitsSpaces(ByVal s As String) As String
'D.McRitchie, .excel, 2004-02-15 modified string for LIKE
'otherwise is same as a posting
'by Harlan Grove, worksheet.functions, 2003-10-20
Dim i As Long, n As Long
n = Len(s)
For i = 1 To n
If Not Mid(s, i, 1) Like "[ 0-9A-Za-z]" Then Mid(s, i, 1) = "^"
Next i
AlphaWithDigitsSpaces = Application.Trim(Application.WorksheetFunction.Substitute(s, "^", ""))
End Function
I tried to gather Harlan's postings on Regular Expressions plus some
additional information he supplied me into a web page:
Extraction of a Group of Digits and Dashes, posted by Harlan Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm
code in
http://www.mvps.org/dmcritchie/excel/code/digitsid.txt
To install see my Getting Started with Macros and User Defined Functions
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Here is a user defined function that will do what you asked for
you will need to install the user defined function (UDF) and
use a helper column.
=AlphaWithDigits(A10)
=personal.xls!AlphaWithDigits(A10)
You asked to remove all spaces.
Function AlphaWithDigits(ByVal s As String) As String
'D.McRitchie, .excel, 2004-02-15 modified string for LIKE
'otherwise is same as a posting
'by Harlan Grove, worksheet.functions, 2003-10-20
Dim i As Long, n As Long
n = Len(s)
For i = 1 To n
If Not Mid(s, i, 1) Like "[0-9 A-Z a-z]" Then Mid(s, i, 1) = " "
Next i
AlphaWithDigits = Application.WorksheetFunction.Substitute(s, " ", "")
End Function
If you decided to retain a representative existing space between words,
you would have to make a few changes.
=AlphaWithDigitsSpaces(A10)
=personal.xls!AlphaWithDigitsSpaces(A10)
Function AlphaWithDigitsSpaces(ByVal s As String) As String
'D.McRitchie, .excel, 2004-02-15 modified string for LIKE
'otherwise is same as a posting
'by Harlan Grove, worksheet.functions, 2003-10-20
Dim i As Long, n As Long
n = Len(s)
For i = 1 To n
If Not Mid(s, i, 1) Like "[ 0-9A-Za-z]" Then Mid(s, i, 1) = "^"
Next i
AlphaWithDigitsSpaces = Application.Trim(Application.WorksheetFunction.Substitute(s, "^", ""))
End Function
I tried to gather Harlan's postings on Regular Expressions plus some
additional information he supplied me into a web page:
Extraction of a Group of Digits and Dashes, posted by Harlan Grove
http://www.mvps.org/dmcritchie/excel/grove_digitsid.htm
code in
http://www.mvps.org/dmcritchie/excel/code/digitsid.txt
To install see my Getting Started with Macros and User Defined Functions
http://www.mvps.org/dmcritchie/excel/getstarted.htm