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? — user8221156
– user8221156, Commented May 25, 2018 at 19:22

Scott Craner · Accepted Answer · 2018-05-28 21:06:04Z

6

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, 2018 at 21:06

answered May 25, 2018 at 19:14

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

dbmitch Over a year ago

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 Over a year ago

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?

Scott Craner Over a year ago

@dbmitch gosh darn it, saw vba and assumed. it is fixed. You are correct. my bad.

Vityata · Accepted Answer · 2018-05-28 14:11:11Z

0

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

answered May 28, 2018 at 14:11

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Collectives™ on Stack Overflow

Access Vba - Reverse a string's contents

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related