Access Vba - Reverse a string's contents

Question

I have a string called str = "12345-5, 12345-4, 12345-3, 12345-2, 12345-1 I need to reverse the string so it looks like this str = "12345-1, 12345-2, 12345-3, 12345-4, 12345-5" I have tried the strReverse method, and it almost did what I wanted...

Sub rev()
    Dim str As String

    str = "12345-5, 12345-4, 12345-3, 12345-2, 12345-1"

    str = StrReverse(Trim(str))
    'turns out to be str = "1-54321 ,2-54321 ,3-54321 ,4-54321 ,5-54321"
End Sub

but it ended up reversing the whole string, should have guessed that. So I'm wondering should I use a regex expression to parse the string and remove the "12345-" and then reverse it and add it back in? I'm not too sure if that would be the best method for my problem. Does anyone know a solution to my problem or could point me in the right direction? Thanks

How did you ever get this result? -- "turns out to be str = "54321-1, 54321-2, 54321-3, 54321-2, 54321-1". Could you explain, please? — , May 25 '18 at 19:22

Scott Craner · Accepted Answer · 2018-05-28T21:06:04.093

5

Use Split then loop backwards through the array:

Sub rev()
    Dim str As String

    str = "12345-5, 12345-4, 12345-3, 12345-2, 12345-1"

    Dim strArr() As String
    strArr = Split(str, ",")

    str = ""

    Dim i As Long
    For i = UBound(strArr) To LBound(strArr) Step -1
        str = str & ", " & Trim(strArr(i))
    Next i

    str = Mid(str, 3)

    Debug.Print str
End Sub

edited May 28 '18 at 21:06

answered May 25 '18 at 19:14

Scott Craner

148,073
10
49
81

I don't think that will compile with the `Application.` in `Application.Trim` But logic looks great otherwise. You could also eliminate the `Trim` by using `Split` on ", " - including the space in the delimiter – dbmitch May 26 '18 at 15:41
Fair enough - but then shouldn't the answer then mention the fact that you're expecting a reference to Excel to use that code? As OP is only using MS-Access he would need to use CreateObject or open Excel wouldn't he? – dbmitch May 28 '18 at 14:30
1

@dbmitch gosh darn it, saw vba and assumed. it is fixed. You are correct. my bad. – Scott Craner May 28 '18 at 21:06

score 0 · Answer 2 · answered May 28 '18 at 14:11

I would do it like this:

Sub TestMe()

    Dim str As String
    str = "12345-5, 12345-4, 12345-3, 12345-2, 12345-1"
    str = StrReverse(str)

    Dim myArr As Variant
    myArr = Split(str, ",")

    Dim newString As String
    Dim myA As Variant

    For Each myA In myArr
        newString = newString & StrReverse(myA) & ","
    Next myA

    newString = Trim(Left(newString, Len(newString) - 1))
    Debug.Print newString

End Sub

Getting this:

12345-1, 12345-2, 12345-3, 12345-4,12345-5

In general, this is quite popular Algorithmic problem, which used to be asked by Google for Junior Developers. Sounding like this - Efficiently reverse the order of the words (not characters) in an array of characters

Access Vba - Reverse a string's contents

2 Answers2