-2

I have eight different variables that can have a value or no value. They look something like this:

A= "h"
B= "(z)"
C= "a"
D= ""
E= "[st]"
F= ""
G= "xx"
H= "(t)"

I need them all together in alphabetical order (must include the brackets):

Aim= "a,h,[st],(t),xx,(z)"

I know how to put them in one string and how to put the "," in the right place.

How do I put them in alphabetical order, while ignoring the brackets?

To exclude the emtpy values and add the commas I use for every value:

If IsEmpty(HH) = False Then HHH = HH.Value & ","

To put them together I use the "&" function

StrHoribez = BBB & CCC & DDD & EEE & FFF & GGG & HHH & III

To remove the last comma I use:

If Right$(StrHoribez, 1) = "," Then StrHoribez = Left$(StrHoribez, Len(StrHoribez) - 1)

The alphabetical sorting can happen in between any of these steps.

How can I approach this? Maybe something with an array but I have no experience using these.

Community
  • 1
  • 1
Anne
  • 47
  • 1
  • 5
  • 1
    Use `Replace()` to remove the non-text characters – Marcucciboy2 Oct 17 '19 at 13:07
  • Does the result have to contain the non-text characters? – riskypenguin Oct 17 '19 at 13:09
  • 1
    You tagged this as an Excel question. You can drop the values into an Excel worksheet, sort and then retrieve them again. – D_Bester Oct 17 '19 at 13:23
  • For VBA sort array: https://stackoverflow.com/questions/152319/vba-array-sort-function – D_Bester Oct 17 '19 at 13:24
  • @D_Bester Thanks for your comment. When you sort it in Excel it puts all the variables with brackets together instead of ignoring it. Also this has to work automatically in the future. I droped the Excel-Tag. Sorry, if that was confusing. – Anne Oct 17 '19 at 13:28

1 Answers1

1

Using Excel worksheet:

  1. Put the values into column A
  2. Put the clean values without brackets into column B
  3. Sort on the values in column B
  4. Retrieve the sorted values from column A including the brackets

Using two-dimensional array:

  1. Put the values into arr(x, 0)
  2. Put the clean values without brackets into arr(x, 1)
  3. Sort on the clean values
  4. Retrieve the sorted values from arr(x, 0) including the brackets

See this for multi-dimensional sorting: Sorting a multidimensionnal array in VBA

D_Bester
  • 5,723
  • 5
  • 35
  • 77