Data Cleansing - Text To Columns With Multiple Columns

This might get you started.

Option Explicit

Sub CleanData()
Dim wsRaw As Worksheet
Dim wsCleaned As Worksheet
Dim rng As Range
Dim arrIn()
Dim arrOut()
Dim I As Long

Dim cnt As Long

    Set wsRaw = Sheets("RAW Data")

    Set rng = wsRaw.Range("A2")

    Do
        arrIn = Range(rng, wsRaw.Cells(rng.Row, Columns.Count).End(xlToLeft)).Value

        For I = 2 To UBound(arrIn, 2)
            cnt = cnt + 1

            ReDim Preserve arrOut(1 To 3, 1 To cnt)

            arrOut(1, cnt) = arrIn(1, 1)
            arrOut(2, cnt) = I - 1
            arrOut(3, cnt) = arrIn(1, I)
        Next I

        Set rng = rng.Offset(1)
        
    Loop Until rng.Value = ""

    Set wsCleaned = Worksheets.Add

    wsCleaned.Range("A1:C1").Value = Array("Unique ID", "Location", "Hospital")

    wsCleaned.Range("A2").Resize(UBound(arrOut, 2), UBound(arrOut, 1)).Value = Application.Transpose(arrOut)

End Sub

1 Open the Visual Basic Editor (ALT+F11).

2 Create a new module (Insert>Module).

3 Copy the code I posted and paste it into the new module.

4 Goto Run>Run Sub or hit F5.