Databases
StackOverflow2013
Postsdbo
POCode an Excel VBA sort with a custom order and a value containing commas
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCode an Excel VBA sort with a custom order and a value containing commas
    text
    copied!<p>In VBA, Excel allows sorting values using the CustomOrder parameter to choose the sequence items are ordered. Unfortunately, the sequence of items is delimited by commas and one of my sort items contains commas. For example, I want to sort the data in the first column by the categories in the second column. The "Air, Land, or Sea" category contains commas.</p> <blockquote> <pre>Data1 Aerospace Data2 Cyberspace Data3 Cyberspace Data4 Air, Land, or Sea Data5 Aerospace Data6 Air, Land, or Sea Data7 Cyberspace </pre> </blockquote> <p>If you record a VBA macro, the code created looks like this:</p> <pre><code>MyWorksheet.Sort.SortFields.Add Key:=Range( _ "B:B"), SortOn:=xlSortOnValues, Order:=xlAscending, _ CustomOrder:= "Cyberspace,Air,Land,or Sea,Aerospace", _ DataOption:=xlSortNormal MyWorksheet.Sort.Apply </code></pre> <p>So, the custom sort order should be "Cyberspace" then "Air, Land, or Sea", then "Aerospace". However, the second category is treated as three categories because of the commas. The rows with "Air, Land, or Sea" get sorted to the bottom because Excel doesn't find a custom sort match for them. Is there a way to get CustomOrder to work with a category that contains embedded commas?</p> <p>I tried putting double quotes around the category and I tried replacing the delimiter commas with semicolons (in the hope Excel would accept a semicolon instead of a comma). Neither worked.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload