Reversing the order of substrings

Dave Edmonds · Jul 13th 2016

Hi Guys,
I have 2 UDFs and then a sub that uses them to split a string everytime it sees a "/" and then concatenate the substrings into a new string with spaces.
My problem is that if I have more than 2 substrings, the order is not correct.

Example: EDMONDS/DAVE will output fine as DAVE EDMONDS but if I try it with EDMONDS/DAVE/STEPHEN it will only output STEPHEN DAVE EDMONDS not DAVE STEPHEN EDMONDS which is how I would like it. I am sure it is a simple change but I just can't see it.
Any help gratefully received.

Code

Function Getname(str As String, lngname As Long)
     
    Dim v As Variant
     
    v = Split(str, "/")
     '// The -1 as arrays are 0 based.
    'If UBound(v) >= lngname Then
       Getname = v(lngname - 1)
    'Else
       ' Getname = ""
   ' End If
     
End Function

Display More

Code

Function Numnames(str As String) As Long
     
    Numnames = UBound(Split(str, "/")) + 1
    
     End Function

Code

Sub Change_Name()
Dim Test As String
Dim Formula1, Formula2 As String
Dim name_string As String
Dim lngname As Long
Dim printname As String
On Error Resume Next
For Each x In Selection
printname = ""
name_string = x
Debug.Print Numnames(name_string)
 
For lngname = 1 To Numnames(name_string)
   Test = Getname(name_string, lngname)
   
   printname = Test & " " & printname
   
Next
 
x.Offset(0, 1) = printname
Next x
End Sub

Display More

Cheers,

Dave

**pike** · Jul 14th 2016

Re: Reversing the order of substrings

Dave,
consider

Code

v = Split(str, "/")
  For i = ubound(v)- 1 To 0 Step -1
     x= x & v(I) & " "

or

Code

Sub RegExp_Reverse()


Dim regEx As Object, RegMatchCollection As Object
Dim Myrange As Range, C As Range, OutPutStr As String
Dim i          As Long
   
 Set regEx = CreateObject("vbscript.regexp")


    With regEx
        .Global = True
        .IgnoreCase = True
        .Pattern ="[a-z]+"
    End With




    Set Myrange = ActiveSheet.Range("B3:B12")


    For Each C In Myrange
        OutPutStr = ""
        Set RegMatchCollection = regEx.Execute(C.Value)


        If RegMatchCollection.Count > 0 Then
            OutPutStr = ""
            For i = RegMatchCollection.Count - 1 To 0 Step -1
                OutPutStr = OutPutStr & " " & RegMatchCollection(i)
            Next
            C.Offset(0, 1) = OutPutStr
        End If
    Next


    Set RegMatchCollection = Nothing
    Set regEx = Nothing
    Set Myrange = Nothing


End Sub

Display More

**rory** · Jul 14th 2016

Re: Reversing the order of substrings

You could just tweak the calling routine:

Code

Sub Change_Name()
    Dim Test                  As String
    Dim Formula1, Formula2    As String
    Dim name_string           As String
    Dim lngname               As Long
    Dim printname             As String
    On Error Resume Next
    For Each x In Selection
        printname = ""
        name_string = x
        Debug.Print Numnames(name_string)




        For lngname = 2 To Numnames(name_string)
            Test = Getname(name_string, lngname)




            printname = printname & " " & Test




        Next
        printname = printname & " " & Getname(name_string, 1)
        x.Offset(0, 1) = printname
    Next x
End Sub

Display More

apo · Jul 14th 2016

Re: Reversing the order of substrings

Here's another possibility..

Code

Private Sub CommandButton1_Click()
    Dim x, y, i As Long
    With [A1].CurrentRegion
        x = Application.Transpose(.Value)
        ReDim y(1 To UBound(x))
        For i = LBound(x) To UBound(x)
        If UBound(Split(x(i), "/")) > 0 Then
            If UBound(Split(x(i), "/")) < 2 Then
                y(i) = Split(x(i), "/")(1) & Chr(32) & Split(x(i), "/")(0)
            Else
                y(i) = Split(x(i), "/")(2) & Chr(32) & Split(x(i), "/")(1) & Chr(32) & Split(x(i), "/")(0)
            End If
        End If
        Next i
        .Offset(, 1).Value = Application.Transpose(y)
    End With
End Sub

Display More

Dave Edmonds · Jul 14th 2016

Re: Reversing the order of substrings

Cheers for all the suggestions guys, I will try them out and feedback.

cheers,
Dave

Reversing the order of substrings

Participate now!

Share