Parse String to adjacent Columns

airforceone

airforceone

Board Regular
Joined
Feb 14, 2022
Messages
196
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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)MichaelSmith(49MaleHospitalizedAlienDRIVER)
James Smith (18/Male/Hospitalized/Alien/JOBLESS)JamesSmith(18MaleHospitalizedAlienJOBLESS)
Robert Smith (18/Male/Hospitalized/Alien/STUDENT)RobertSmith(18MaleHospitalizedAlienSTUDENT)
Maria Garcia (43/Female/Hospitalized/Alien/FARMER)MariaGarcia(43FemaleHospitalizedAlienFARMER)
David Smith (15/Female/Deceased/Alien/STUDENT)DavidSmith(15FemaleDeceasedAlienSTUDENT)
Maria Rodriguez (46/Female/Hospitalized/Alien/FARMER)MariaRodriguez(46FemaleHospitalizedAlienFARMER)
Mary Smith (2/Male/Hospitalized/Alien/JOBLESS)MarySmith(2MaleHospitalizedAlienJOBLESS)
Gerald Golbuno (22/Male/Hospitalized/Alien/STUDENT)GeraldGolbuno(22MaleHospitalizedAlienSTUDENT)
Maria Hernandez (34/Female/Hospitalized/Alien/JOBLESS)MariaHernandez(34FemaleHospitalizedAlienJOBLESS)
Maria Martinez (37/Female/Hospitalized/Alien/JOBLESS)MariaMartinez(37FemaleHospitalizedAlienJOBLESS)
James Johnson (40/Male/Hospitalized/Alien/JOBLESS)JamesJohnson(40MaleHospitalizedAlienJOBLESS)
Maria Michael (37/Female/Hospitalized/Alien/JOBLESS)MariaMichael(37FemaleHospitalizedAlienJOBLESS)
James Biggins (38/Male/Hospitalized/Alien/JOBLESS)JamesBiggins(38MaleHospitalizedAlienJOBLESS)
CODE RESULT


Expected Result

Michael Smith (49/Male/Hospitalized/Alien/DRIVER)Michael Smith49MaleHospitalizedAlienDRIVER
James Smith (18/Male/Hospitalized/Alien/JOBLESS)James Smith18MaleHospitalizedAlienJOBLESS
Robert Smith (18/Male/Hospitalized/Alien/STUDENT)Robert Smith18MaleHospitalizedAlienSTUDENT
Maria Garcia (43/Female/Hospitalized/Alien/FARMER)Maria Garcia43FemaleHospitalizedAlienFARMER
David Smith (15/Female/Deceased/Alien/STUDENT)David Smith15FemaleDeceasedAlienSTUDENT
Maria Rodriguez (46/Female/Hospitalized/Alien/FARMER)Maria Rodriguez46FemaleHospitalizedAlienFARMER
Mary Smith (2/Male/Hospitalized/Alien/JOBLESS)Mary Smith2MaleHospitalizedAlienJOBLESS
Gerald Golbuno (22/Male/Hospitalized/Alien/STUDENT)Gerald Golbuno22MaleHospitalizedAlienSTUDENT
Maria Hernandez (34/Female/Hospitalized/Alien/JOBLESS)Maria Hernandez34FemaleHospitalizedAlienJOBLESS
Maria Martinez (37/Female/Hospitalized/Alien/JOBLESS)Maria Martinez37FemaleHospitalizedAlienJOBLESS
James Johnson (40/Male/Hospitalized/Alien/JOBLESS)James Johnson40MaleHospitalizedAlienJOBLESS
Maria Michael (37/Female/Hospitalized/Alien/JOBLESS)Maria Michael37FemaleHospitalizedAlienJOBLESS
James Biggins (38/Male/Hospitalized/Alien/JOBLESS)James Biggins38MaleHospitalizedAlienJOBLESS
EXPECTED RESULT




VBA Code: 
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
TextToColumns?
Code: 
Sub test()
    Columns(1).TextToColumns [b1], 1, Other:=True, OtherChar:="("
    Columns(3).Replace ")", "", 2
    Columns(3).TextToColumns [c1], 1, Other:=True, OtherChar:="/"
End Sub
 
Upvote 0
Fuji said:
TextToColumns?
Code: 
Sub test()
    Columns(1).TextToColumns [b1], 1, Other:=True, OtherChar:="("
    Columns(3).Replace ")", "", 2
    Columns(3).TextToColumns [c1], 1, Other:=True, OtherChar:="/"
End Sub
Click to expand...
thanks mate for the time kindly see expected result it will parse all entry
 
Upvote 0
Here's the result from the code.
Book1
ABCDEFG
1Michael Smith (49/Male/Hospitalized/Alien/DRIVER)Michael Smith 49MaleHospitalizedAlienDRIVER
2James Smith (18/Male/Hospitalized/Alien/JOBLESS)James Smith 18MaleHospitalizedAlienJOBLESS
3Robert Smith (18/Male/Hospitalized/Alien/STUDENT)Robert Smith 18MaleHospitalizedAlienSTUDENT
4Maria Garcia (43/Female/Hospitalized/Alien/FARMER)Maria Garcia 43FemaleHospitalizedAlienFARMER
5David Smith (15/Female/Deceased/Alien/STUDENT)David Smith 15FemaleDeceasedAlienSTUDENT
6Maria Rodriguez (46/Female/Hospitalized/Alien/FARMER)Maria Rodriguez 46FemaleHospitalizedAlienFARMER
7Mary Smith (2/Male/Hospitalized/Alien/JOBLESS)Mary Smith 2MaleHospitalizedAlienJOBLESS
8Gerald Golbuno (22/Male/Hospitalized/Alien/STUDENT)Gerald Golbuno 22MaleHospitalizedAlienSTUDENT
9Maria Hernandez (34/Female/Hospitalized/Alien/JOBLESS)Maria Hernandez 34FemaleHospitalizedAlienJOBLESS
10Maria Martinez (37/Female/Hospitalized/Alien/JOBLESS)Maria Martinez 37FemaleHospitalizedAlienJOBLESS
11James Johnson (40/Male/Hospitalized/Alien/JOBLESS)James Johnson 40MaleHospitalizedAlienJOBLESS
12Maria Michael (37/Female/Hospitalized/Alien/JOBLESS)Maria Michael 37FemaleHospitalizedAlienJOBLESS
13James Biggins (38/Male/Hospitalized/Alien/JOBLESS)James Biggins 38MaleHospitalizedAlienJOBLESS
Sheet1
 
Upvote 0
with the same code and data mine is

Michael Smith (49/Male/Hospitalized/Alien/DRIVER)MichaelSmith49/Male/Hospitalized/Alien/DRIVER)
James Smith (18/Male/Hospitalized/Alien/JOBLESS)JamesSmith18/Male/Hospitalized/Alien/JOBLESS)
Robert Smith (18/Male/Hospitalized/Alien/STUDENT)RobertSmith18/Male/Hospitalized/Alien/STUDENT)
Maria Garcia (43/Female/Hospitalized/Alien/FARMER)MariaGarcia43/Female/Hospitalized/Alien/FARMER)
David Smith (15/Female/Deceased/Alien/STUDENT)DavidSmith15/Female/Deceased/Alien/STUDENT)
Maria Rodriguez (46/Female/Hospitalized/Alien/FARMER)MariaRodriguez46/Female/Hospitalized/Alien/FARMER)
Mary Smith (2/Male/Hospitalized/Alien/JOBLESS)MarySmith2/Male/Hospitalized/Alien/JOBLESS)
Gerald Golbuno (22/Male/Hospitalized/Alien/STUDENT)GeraldGolbuno22/Male/Hospitalized/Alien/STUDENT)
Maria Hernandez (34/Female/Hospitalized/Alien/JOBLESS)MariaHernandez34/Female/Hospitalized/Alien/JOBLESS)
Maria Martinez (37/Female/Hospitalized/Alien/JOBLESS)MariaMartinez37/Female/Hospitalized/Alien/JOBLESS)
James Johnson (40/Male/Hospitalized/Alien/JOBLESS)JamesJohnson40/Male/Hospitalized/Alien/JOBLESS)
Maria Michael (37/Female/Hospitalized/Alien/JOBLESS)MariaMichael37/Female/Hospitalized/Alien/JOBLESS)
James Biggins (38/Male/Hospitalized/Alien/JOBLESS)JamesBiggins38/Male/Hospitalized/Alien/JOBLESS)
 
Upvote 0
Fuji said:
Here's the result from the code.
Book1
ABCDEFG
1Michael Smith (49/Male/Hospitalized/Alien/DRIVER)Michael Smith 49MaleHospitalizedAlienDRIVER
2James Smith (18/Male/Hospitalized/Alien/JOBLESS)James Smith 18MaleHospitalizedAlienJOBLESS
3Robert Smith (18/Male/Hospitalized/Alien/STUDENT)Robert Smith 18MaleHospitalizedAlienSTUDENT
4Maria Garcia (43/Female/Hospitalized/Alien/FARMER)Maria Garcia 43FemaleHospitalizedAlienFARMER
5David Smith (15/Female/Deceased/Alien/STUDENT)David Smith 15FemaleDeceasedAlienSTUDENT
6Maria Rodriguez (46/Female/Hospitalized/Alien/FARMER)Maria Rodriguez 46FemaleHospitalizedAlienFARMER
7Mary Smith (2/Male/Hospitalized/Alien/JOBLESS)Mary Smith 2MaleHospitalizedAlienJOBLESS
8Gerald Golbuno (22/Male/Hospitalized/Alien/STUDENT)Gerald Golbuno 22MaleHospitalizedAlienSTUDENT
9Maria Hernandez (34/Female/Hospitalized/Alien/JOBLESS)Maria Hernandez 34FemaleHospitalizedAlienJOBLESS
10Maria Martinez (37/Female/Hospitalized/Alien/JOBLESS)Maria Martinez 37FemaleHospitalizedAlienJOBLESS
11James Johnson (40/Male/Hospitalized/Alien/JOBLESS)James Johnson 40MaleHospitalizedAlienJOBLESS
12Maria Michael (37/Female/Hospitalized/Alien/JOBLESS)Maria Michael 37FemaleHospitalizedAlienJOBLESS
13James Biggins (38/Male/Hospitalized/Alien/JOBLESS)James Biggins 38MaleHospitalizedAlienJOBLESS
Sheet1
Click to expand...

mate my office version is 2019 and 2016 not 365
 
Upvote 0
Perhaps, the settings are not cleared.
Code: 
Sub test()
    Columns(1).TextToColumns Destination:=[b1], DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="(", TrailingMinusNumbers:=True
    Columns(3).Replace ")", "", 2
    Columns(3).TextToColumns [c1], 1, Other:=True, OtherChar:="/"
End Sub
 
Upvote 0
Solution
Fuji said:
Perhaps, the settings are not cleared.
Code: 
Sub test()
    Columns(1).TextToColumns Destination:=[b1], DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="(", TrailingMinusNumbers:=True
    Columns(3).Replace ")", "", 2
    Columns(3).TextToColumns [c1], 1, Other:=True, OtherChar:="/"
End Sub
Click to expand...
works mate, hopefully it will gel with my main code!
thanks mate, definitely won't be the last :)
 
Upvote 0
airforceone said:
works mate,
Click to expand...
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.

VBA Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

airforceone
  • Solved
Populate Rows & Columns by Comma Split
2
Replies
12
Views
229
airforceone
airforceone
airforceone
  • Solved
Populate Rows & Columns by Comma Split (DYNAMIC VERSION ver.2)
Replies
4
Views
125
airforceone
airforceone
M
  • Question
Formula for number of visits for each account number
Replies
3
Views
677
Alex Blakenburg
A
O
  • Question
Create a TimeLine Graph
Replies
0
Views
2K
Omer_K
O

Forum statistics

Threads
1,221,499
Messages
6,160,169
Members
451,629
Latest member
MNexcelguy19

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top