Excel VBA userform paste text in expanded format not just to one cell

Question

I'm not sure if the title is the right way to address the question, but anyway,

The user of my userform pastes a block of data into it. I've managed to correctly copy the data from the form so far into a single cell, but I want the data to paste so that it appears as though the user right-clicked and expanded it into numerous cells.

Exists a way to accomplish this? Bad explanation, I know; for clarification, view the screenshots.

Private Sub IPButton2_Click()
ActiveWorkbook.Sheets("Site_IP_List").Activate
InputForm.Hide
Dim text As String
text = TextBoxIPData.text
Range("D1").Value = text

Form: Form Image

What I'm looking for: Right Click, Intended Result

What it's doing: Form Result

I have tried different ways to express the data and have it input, but it all ends up stuck in one cell.

Answer 1

It should work if you simply supply your string to this sub in place of its existing sValue. A few items might need to be changed to fit your data.
One risk of presenting photographs rather than text is simply that.
You might need to add a leading zero back in after the trim(txtstr) line if there are several of them. Beginning at the top: " " & TxtStr & TxtStr

Option Explicit
Sub TextToArrayToRange()
    
    Dim RG As Range         'Output range
    Dim TxtArr1             'Text Array 1 (broken into lines)
    Dim TxtArr2             'Text Array 2 (broken into words)
    Dim TxtStr As String    'Original String
    Dim LB As String        'LineBreak
    Dim Y As Long           'Iterate Y axis position
    Dim X As Long           'Iterate X axis position
    Dim lRows As Long       'Rows Count
    Dim lCols As Long       'Columns Count
    
    ' > Choose your linebreak character chr(13), chr(10)
    LB = Chr(10)
    
    ' > Choose output range
    Set RG = Sheet1.Range("B2")
    
    ' > Make txtstr = your text string
    TxtStr = "TEST-TEST-1 AA:BB:CC:DD:EE:FF 100.100.100.100" & LB & _
            "TEST-TEST-2 AA:BB:CC:DD:EE:FF 100.100.100.100" & LB & _
            "TEST-TEST-3 AA:BB:CC:DD:EE:FF 100.100.100.100" & LB & _
            "TEST-TEST-4 AA:BB:CC:DD:EE:FF 100.100.100.100" & LB & _
            "TEST-TEST-5 AA:BB:CC:DD:EE:FF 100.100.100.100" & LB & _
            "TEST-TEST-6 AA:BB:CC:DD:EE:FF 100.100.100.100" & LB & _
            "TEST-TEST-7 AA:BB:CC:DD:EE:FF 100.100.100.100"
    TxtStr = Trim(TxtStr)   'Remove dupe spaces
    
    'Debug.Print TxtStr
    TxtArr1 = Split(TxtStr, LB)
    lRows = Len(TxtStr) - Len(Replace(TxtStr, LB, ""))
    lCols = Len(TxtArr1(0)) - Len(Replace(TxtArr1(0), " ", ""))
    
    ReDim TxtArr2(0 To lRows, 0 To lCols)
    
    For Y = 0 To lRows
        'Debug.Print TxtArr1(I)
        For X = 0 To lCols
            TxtArr2(Y, X) = Split(TxtArr1(Y), " ")(X)
            Debug.Print TxtArr2(Y, X)
        Next X
    Next Y
    
    Set RG = RG.Resize(lRows + 1, lCols + 1)
    RG = TxtArr2
    RG.EntireColumn.AutoFit
    
End Sub

Current Output: