Parse String to adjacent Columns

[airforceone]

Good day mate, need some guidance here
1. got a code with some loop error and
2. would like to parse the cell value into adjacent column

attached here is the source, Code Result and Expected Result (Hopefully)

source

Michael Smith (49/Male/Hospitalized/Alien/DRIVER)
James Smith (18/Male/Hospitalized/Alien/JOBLESS)
Robert Smith (18/Male/Hospitalized/Alien/STUDENT)
Maria Garcia (43/Female/Hospitalized/Alien/FARMER)
David Smith (15/Female/Deceased/Alien/STUDENT)
Maria Rodriguez (46/Female/Hospitalized/Alien/FARMER)
Mary Smith (2/Male/Hospitalized/Alien/JOBLESS)
Gerald Golbuno (22/Male/Hospitalized/Alien/STUDENT)
Maria Hernandez (34/Female/Hospitalized/Alien/JOBLESS)
Maria Martinez (37/Female/Hospitalized/Alien/JOBLESS)
James Johnson (40/Male/Hospitalized/Alien/JOBLESS)
Maria Michael (37/Female/Hospitalized/Alien/JOBLESS)
James Biggins (38/Male/Hospitalized/Alien/JOBLESS)

Code Result (With some kind of Loop error, when run)

Michael Smith (49/Male/Hospitalized/Alien/DRIVER)	Michael	Smith	(49	Male	Hospitalized	Alien	DRIVER)
James Smith (18/Male/Hospitalized/Alien/JOBLESS)	James	Smith	(18	Male	Hospitalized	Alien	JOBLESS)
Robert Smith (18/Male/Hospitalized/Alien/STUDENT)	Robert	Smith	(18	Male	Hospitalized	Alien	STUDENT)
Maria Garcia (43/Female/Hospitalized/Alien/FARMER)	Maria	Garcia	(43	Female	Hospitalized	Alien	FARMER)
David Smith (15/Female/Deceased/Alien/STUDENT)	David	Smith	(15	Female	Deceased	Alien	STUDENT)
Maria Rodriguez (46/Female/Hospitalized/Alien/FARMER)	Maria	Rodriguez	(46	Female	Hospitalized	Alien	FARMER)
Mary Smith (2/Male/Hospitalized/Alien/JOBLESS)	Mary	Smith	(2	Male	Hospitalized	Alien	JOBLESS)
Gerald Golbuno (22/Male/Hospitalized/Alien/STUDENT)	Gerald	Golbuno	(22	Male	Hospitalized	Alien	STUDENT)
Maria Hernandez (34/Female/Hospitalized/Alien/JOBLESS)	Maria	Hernandez	(34	Female	Hospitalized	Alien	JOBLESS)
Maria Martinez (37/Female/Hospitalized/Alien/JOBLESS)	Maria	Martinez	(37	Female	Hospitalized	Alien	JOBLESS)
James Johnson (40/Male/Hospitalized/Alien/JOBLESS)	James	Johnson	(40	Male	Hospitalized	Alien	JOBLESS)
Maria Michael (37/Female/Hospitalized/Alien/JOBLESS)	Maria	Michael	(37	Female	Hospitalized	Alien	JOBLESS)
James Biggins (38/Male/Hospitalized/Alien/JOBLESS)	James	Biggins	(38	Male	Hospitalized	Alien	JOBLESS)
CODE RESULT

Expected Result

Michael Smith (49/Male/Hospitalized/Alien/DRIVER)	Michael Smith	49	Male	Hospitalized	Alien	DRIVER
James Smith (18/Male/Hospitalized/Alien/JOBLESS)	James Smith	18	Male	Hospitalized	Alien	JOBLESS
Robert Smith (18/Male/Hospitalized/Alien/STUDENT)	Robert Smith	18	Male	Hospitalized	Alien	STUDENT
Maria Garcia (43/Female/Hospitalized/Alien/FARMER)	Maria Garcia	43	Female	Hospitalized	Alien	FARMER
David Smith (15/Female/Deceased/Alien/STUDENT)	David Smith	15	Female	Deceased	Alien	STUDENT
Maria Rodriguez (46/Female/Hospitalized/Alien/FARMER)	Maria Rodriguez	46	Female	Hospitalized	Alien	FARMER
Mary Smith (2/Male/Hospitalized/Alien/JOBLESS)	Mary Smith	2	Male	Hospitalized	Alien	JOBLESS
Gerald Golbuno (22/Male/Hospitalized/Alien/STUDENT)	Gerald Golbuno	22	Male	Hospitalized	Alien	STUDENT
Maria Hernandez (34/Female/Hospitalized/Alien/JOBLESS)	Maria Hernandez	34	Female	Hospitalized	Alien	JOBLESS
Maria Martinez (37/Female/Hospitalized/Alien/JOBLESS)	Maria Martinez	37	Female	Hospitalized	Alien	JOBLESS
James Johnson (40/Male/Hospitalized/Alien/JOBLESS)	James Johnson	40	Male	Hospitalized	Alien	JOBLESS
Maria Michael (37/Female/Hospitalized/Alien/JOBLESS)	Maria Michael	37	Female	Hospitalized	Alien	JOBLESS
James Biggins (38/Male/Hospitalized/Alien/JOBLESS)	James Biggins	38	Male	Hospitalized	Alien	JOBLESS
EXPECTED RESULT

Private Sub Parse_String()

    Dim rngSource As Range
    Dim LastRow As Long, iLoop As Long
    Dim LastColHeader As String
    
    LastRow = Sheets("SOURCE").UsedRange.Rows.Count

    With Sheets("SOURCE")
        Set rngSource = Sheets("SOURCE").Range("A1:A" & LastRow)
    End With

    Dim Targetcel As Range
    For Each Targetcel In rngSource
        Targetcel.Value = WorksheetFunction.Substitute(Targetcel.Value, "-", ", ")
    Next Targetcel

    For iLoop = 1 To LastRow
        LastColHeader = Split(Cells(iLoop, 2).Address, "$")(1)
        rngSource.TextToColumns _
                Destination:=Range(LastColHeader & iLoop & ":" & LastColHeader & iLoop & ":" & LastColHeader & iLoop & ":" & LastColHeader & iLoop & ":" & LastColHeader & iLoop), _
                DataType:=xlDelimited, _
                Tab:=False, _
                Semicolon:=False, _
                Comma:=False, _
                Space:=True, _
                Other:=True, _
                OtherChar:="/"
    Next iLoop
    
End Sub     ' Parse_String

 

[airforceone]

Not sure what you are doing with the results (if anything) or whether it matters to you but with your sample data, the code leaves all the values in the name column with a trailing space.

If interested, here is another option that addresses that issue and only requires a single text to columns.

Sub TTC()
  With Range("B1:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Value = Application.Substitute(Application.Substitute(.Offset(, -1).Value, " (", "/"), ")", "")
    .TextToColumns , 1, , , 0, 0, 0, 0, 1, "/"
  End With
End Sub

yes did see it (trailing space) after the name and the code you've given seems to be alot more faster!
but given that I'm new at excel vba the first code seems more readable and and alot easier to understand

 

[Peter_SSs]

My code transfers col A to col B & at the same time replaces the " (" with a "/" and removes the final ")".
Then it just does a text to columns on col B with "/" as the delimiter.

Still, you are right to go with the code that you are most comfortable with.

Thanks for the follow-up though.

 

[airforceone]

My code transfers col A to col B & at the same time replaces the " (" with a "/" and removes the final ")".
Then it just does a text to columns on col B with "/" as the delimiter.

Still, you are right to go with the code that you are most comfortable with.

Thanks for the follow-up though.

no problem mate
much appreciated someone actually checking on someone's submitted post/answer though working, might have something more faster and less code to issue!
cheers mate till my/our next query