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!
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!
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
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
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
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.
Re: Sort Cell Data Speparated By "|"
You're wuite welcome
Don’t have an account yet? Register yourself now and be a part of our community!