Search code examples
excelvbaconcatenationuserform

Concatenate one(!) row with separator

I have following data:

Surname: John Walter Friedrich
Name: Waterford Harrington from Downhill

Each Word (separated by " ") in a new Column (here: "Surname:"=A1, "John"=B1, ... "Friedrich"=D1).

I have a Userform with textboxes.

What I want: Copy the Words from cells A1:(last not empty cell in row) into textbox in Userform. -> Textbox filled with: "John Walter Friedrich" (separated by " ")

I tried various codes, but didn't found anything what matches my problem.

What I don't want: Combine rows! Thats what I found, but I have for one textbox just one row. So I dont want do go through each row until one is empty (because the next one is full).

PS: I can't get the data in my Excel sheet separated with " " to copy it. Problem: First Word (Surname or Name) should not be copied!

Solution

  • Test this:

    Public Sub Test()

    Dim InputText As String
    InputText = "Surname: John Walter Friedrich"  'or Worksheets("Sheet1").Range("A1").Value

    MsgBox Right$(InputText, Len(InputText) - InStr(1, InputText, " "))

End Sub