Sort Cell Data Seperated By Pipe Separator (|)

DANDERSON · Sep 29th 2006

I have a multiple cells with values similar to the following:
P442-C|P642-C|P342-C|P842-C

I need to reorder the value to read:
P842-C|P642-C|P442-C|P342-C

With VBA, could you give me some advice.
Thanks!

Wigi · Sep 29th 2006

Re: Sort Cell Data Speparated By "|"

Obviously, you will need to:

- split the cells based on the pipe separator
- sort them
- put them back together

Wigi

JF · Sep 29th 2006

Re: Sort Cell Data Speparated By "|"

Well, using the macro recorder I got the below code. I started with your
example (P442-C|P642-C|P342-C|P842-C) in A1. Coppied it to D1 and then did a text to column, copied and pasted special/transpose to d3, sorted,
then reconstructed in d8 and copied and pasted special/value to A2

You should be able to modify to loop through your sheet or copy to different location

Code

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/29/2006 by foxjeg
'


'
    Range("A1").Select
    Selection.Copy
    Range("D1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=True, OtherChar:= _
        "|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), _
        TrailingMinusNumbers:=True
    Range("D1:G1").Select
    Selection.Copy
    Range("D3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("D3"), Order1:=xlDescending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("D8").Select
    ActiveCell.FormulaR1C1 = "=R[-5]C&""|""&R[-4]C&""|""&R[-3]C&""|""&R[-2]C"
    Range("D8").Select
    Selection.Copy
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Display More

DANDERSON · Sep 29th 2006

Re: Sort Cell Data Speparated By "|"

Awesome! Learn something new everyday. Thank you for the information and direction. I can use this technique to do exactly what I want.

Thanks again.

JF · Sep 29th 2006

Re: Sort Cell Data Speparated By "|"

You're wuite welcome

Sort Cell Data Seperated By Pipe Separator (|)

Participate now!

Share