How to join characters in an array into a string?

Question

I'm trying to transform a text into a Spongebob mocking style text, like this:

Hello World! = hElLo WoRlD.

I split the string into an array and converted the letters, but I cant only print them as an array.

How do I join the characters into a string?

Private Sub CommandButton1_Click()
Dim Word As String
Dim i As Integer
Dim Letters() As String

Word = Sheet1.Range("A1").Value

Letters = Split(StrConv(Word, 64), Chr(0))
For i = 0 To Len(Word) - 1 Step 2
    Debug.Print StrConv(Letters(i), 1)
    Debug.Print StrConv(Letters(i + 1), 2)
Next i

End Sub

Or using a temp string variable and concatenating each character to the temp string in the loop? — Scott Craner
– Scott Craner, Commented Dec 9, 2019 at 15:41
@BigBen I used this after next i Result = Join(lower, "") Debug.Print Result after declaring lower as an array but it didnt work — Vbnoob
– Vbnoob, Commented Dec 9, 2019 at 16:01

BigBen · Accepted Answer · 2019-12-09 16:37:51Z

1

Use Join to concatenate the array of characters. You can modify the contents of Letters directly to get the lower-case/upper-case alternation, something like this:

Private Sub CommandButton1_Click()
    Dim Word As String
    Word = Sheet1.Range("A1").Value

    Dim Letters() As String
    Letters = Split(StrConv(Word, 64), Chr(0))

    Dim i As Long
    For i = LBound(Letters) To UBound(Letters)
        If i Mod 2 = 0 Then
            Letters(i) = StrConv(Letters(i), 2) '<~ or `LCase$`
        Else
            Letters(i) = StrConv(Letters(i), 1) '<~ or `UCase$`
        End If
    Next

    Debug.Print Join(Letters, "")
End Sub

edited Dec 9, 2019 at 16:37

answered Dec 9, 2019 at 16:28

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

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

1 Comment

Vbnoob Over a year ago

This is way better than the idea I was going for. it works! thank you so much

xTwisteDx · Accepted Answer · 2019-12-09 16:04:34Z

1

For your code all you need to do is create a new variable to hold your newly created string.

DIM allLetters As String

Then join the Letters() using the Join() method.

allLetters = Join(Letters)

Here's the documentation on joining.

https://www.excelfunctions.net/vba-join-function.html

' Join together the strings "John", "Paul" and "Smith".
Dim fullName As String
Dim names( 0 to 2 ) As String
names(0) = "John"
names(1) = "Paul"
names(2) = "Smith"
fullName = Join( names )
' The variable fullName is now set to "John Paul Smith"

Reference

VBA JOIN Function. (n.d.). Retrieved from https://www.excelfunctions.net/vba-join-function.html

answered Dec 9, 2019 at 16:04

xTwisteDx

2,5201 gold badge13 silver badges39 bronze badges

1 Comment

Vbnoob Over a year ago

Thank you for your help, I didn't know how to properly use the join method. @BigBen gave me how and where to add it to the script. Now it works!

Collectives™ on Stack Overflow

How to join characters in an array into a string?

2 Answers 2

1 Comment

Reference

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Reference

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related