Sorting letters within a string alphabetically

stephanfassbender

Forums

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

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

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

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

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