Excel alphanumberic sort
Hello,
If your data is in column A starting from row 2 then enter into
B2:
=regexpreplace($A2,"^(\D*)(\d+)(.*)$","$1")
C2:
=--regexpreplace($A2,"^(\D*)(\d+)(.*)$","$2")
D2:
=regexpreplace($A2,"^(\D*)(\d+)(.*)$","$3")
copy down as far as necessary and sort by columns C, B and D
Regards,
Bernd
PS: [not my invention:]
Function RegExpReplace(ByVal SourceString As String, _
ByVal Pattern As String, ByVal ReplaceString As String, _
Optional ByVal IgnoreCase As Boolean = False, _
Optional ByVal GlobalReplace As Boolean = False, _
Optional ByVal MultiLine As Boolean = False) As String
Dim objRE As Object
Set objRE = CreateObject("vbscript.regexp")
objRE.Pattern = Pattern
objRE.IgnoreCase = IgnoreCase
objRE.Global = GlobalReplace
objRE.MultiLine = MultiLine
RegExpReplace = objRE.Replace(SourceString, ReplaceString)
Set objRE = Nothing
End Function
|