Skip to main content

Sorting letters within a string alphabetically

Hey everybody,

I hope that someone can help me with my problem:
I'm trying to built a function that can sort the letters within a string alphabetically.
I figured out how to do it in Excel, but I don't know how to get it done in IDEA Basic.

Option Explicit
Public Function sortieren(zelle) As String
Dim objAL As Object
Dim I As Integer
Set objAL = CreateObject("System.Collections.Arraylist")
With objAL
For I = 1 To Len(zelle.Text)
.Add (Mid(zelle.Text, I, 1))
Next
.Sort
sortieren = Join(.toArray, "")
End With
Set objAL = Nothing
End Function

Thanks a lot

Brian Element Mon, 11/19/2018 - 20:22

Hi Stephan,

Here is an example using the internal IDEAScripting array and then sorting it.  This array is for integers but it can be easily changed for strings.  I have used the collections in the past but usually I stick with the internal array structure if I can.

Here is an example of a list of random number before being sorted:

and after:

Here is the code to generate the array and do the sort:


Option Explicit
	
Sub Main
	Dim iArray(20) As Integer
	Dim i As Integer
	Dim msg As String
	For i = 0 To 20
		iArray(i) = Int(1 + Rnd() * 100)
		msg = msg & iArray(i) & Chr(10) & Chr(13)
	Next i
	msg = "Before sort:" & Chr(10) & Chr(13) & msg
	MsgBox msg
	Call sortArray(iArray)
	msg = ""
	For i = 0 To 20
		msg = msg & iArray(i) & Chr(10) & Chr(13)
	Next i

	msg = "After sort:" & Chr(10) & Chr(13) & msg
	MsgBox msg
End Sub

Private Function sortArray(MyArray() As Integer)
	Dim lLoop, lLoop2 As Integer
	Dim str1, str2 As Integer
	For lLoop = 0 To UBound(MyArray)
	
		For lLoop2 = lLoop To UBound(MyArray)
		
			If MyArray(lLoop2) < MyArray(lLoop) Then
			
				str1 = MyArray(lLoop)
				str2 = MyArray(lLoop2)
				MyArray(lLoop) = str2
				MyArray(lLoop2) = str1
			
			End If
		
		Next lLoop2
	
	Next lLoop

End Function

stephanfassbender Tue, 11/20/2018 - 04:16

Hi Brian,
thanks for your message :-)

I played around with your script for a while but I don't seem to be able to get what I want.
Is there a way to build a user defined function which I can use in an added field of my table to get the following result: "Stephan" --> "aehnpSt"
This would be exactly the same what I managed to do in Excel.

Maybe you can help me out again

Brian Element Tue, 11/20/2018 - 04:45

In reply to by stephanfassbender

Hi Stephan,

Any chance you can give me an example of what you are trying to do, such as a before and after as I am not sure what you are looking for right now and I will try and help out once I understand exactly what are trying to accomplish.

Thanks

Brian

stephanfassbender Tue, 11/20/2018 - 04:56

Hi Brian,
what I'm trying to do is adding a field to my existing table with the letters sorted alphabetically like here (Column 1 "Name1" exists in my table and Column 2 "Sortiert" will be added using the sorting function):

Brian Element Tue, 11/20/2018 - 05:05

In reply to by stephanfassbender

Thanks Stephan, I understand now.  Is this part of a script as you can do this with a Custom Function as part of the equation editor so you don't actually need a script.  The only problem with doing sorting is an A is not the same as an a, so your first item would probably be AGHSaccehhmnnoortu as capitals are sorted as lower than lower case (I think).  

Brian

Brian Element Tue, 11/20/2018 - 13:10

In reply to by stephanfassbender

Hi Stephan,

Try out this custom function.  Just save it in your projects Custom Functions.ILB folder and the syntax to use it in the Equation editor is #SortChar(MY_FIELD).  It seems to be giving me the same result as what you posted.

Brian

stephanfassbender Wed, 11/21/2018 - 02:32

In reply to by Brian Element

Hey Brian,
the function works great, thank you so much :-)
Stephan