Splitting Info in a cell to separate rows

[Leesha]

Hi,
It seems like I'm always trying to split up data in a cell. Normally it's been pretty definable, meaning I need to split the information after a space or a word etc. In this situation the data in the cell is separated by a comma. The issue is that there could be anywhere from 1 item in the cell to multiple. I'm not sure if this is even feasible or if I'm better off doing in excel and then importing. Either way I don't begin to know what the code would look like.

For example this is how one would appear:
Symptom(s):Arm Problems (Right), Asthma, Back Pain (Upper), Bleeding/Blood Disorders, Cancer, Hodgkin's Disease, Leukemia, Paralysis, Paresthesia, Rheumatoid Arthritis

I would need each symptom broken out separately. In another case, there may only be 1 or 2 symptoms.

I need to be able to have one symptom per row in a column, IE:

Symtpom(s) Arm Problems (Right)
Symptom(s) Asthma
Symptom(s) Back Pain (Upper)
And so on.

Is this even possible?
Thanks,
Leesha

[HansV]

You write "I'm not sure if this is even feasible or if I'm better off doing in excel and then importing."
Does that mean that it's actually a problem in another application (Access)? You posted the question in the Excel forum.

[Leesha]

OMG, my brain is fried. I started putting into Access and them switched to the Excel forum as I figured it would be more applicable in Excel. Then again what do I know lol. Ultimately the data will end up in Access. I don't have a preference as where the actual splitting takes place. Am just praying it doesn't need to be done manually, else I'm saying no to this job.
Thanks!

[HansV]

Does the table contain other fields?
If so: what should happen to those fields? Should the values be repeated? Or ...?

[Leesha]

No it doesn't contain other fields. Just 2 columns.

[HansV]

2 columns? Please explain.

[Leesha]

Yes the first column is called "Symptom" and the Second is called "Emotional Routes". The data that is being split is in Emotional Routes.

[HansV]

So, what do you want to happen to the Symptom column when we add rows to accommodate the split values in the Emotional Routes column?

[Leesha]

The word in the symptom column would repeat for each row added. So if there are 7 items extracted to 7 different rows, the Symptom would be the same for each row.

Code: Select all

SYMPTOM             EMOTIONAL ROUTE
Accident Prone	    Aggression, Fear of Speaking Out, Rebellion, Unprotected, Violence, Wrong

Would result in below

Code: Select all

SYMPTOM	           EMOTIONAL ROUTE
Accident Prone	   Aggression
Accident Prone	   Fear of Speaking Out
Accident Prone	   Rebellion
Accident Prone	   Unprotected
Accident Prone	   Violence
Accident Prone	   Wrong

[HansV]

Here is code you can use:

Code: Select all

Sub SplitField()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim arrRoutes() As String
    Dim strSymptom As String
    Dim i As Long
    Set dbs = CurrentDb
    ' Change name of table as needed
    Set rst = dbs.OpenRecordset("tblSymptoms", dbOpenDynaset)
    Do While Not rst.EOF
        arrRoutes = Split(rst![Emotional Route], ",")
        If UBound(arrRoutes) > 0 Then
            rst.Edit
            rst![Emotional Route] = Trim(arrRoutes(0))
            rst.Update
            strSymptom = rst!Symptom
            For i = 1 To UBound(arrRoutes)
                rst.AddNew
                rst!Symptom = strSymptom
                rst![Emotional Route] = Trim(arrRoutes(i))
                rst.Update
            Next i
        End If
        rst.MoveNext
    Loop
    rst.Close
End Sub

See the attached sample database. It contains a table tblSymptoms with your two example records, and a table tblSymptoms_SampleResult that shows the result of running the code.

SplitData.zip

[StuartR]

In this example how do you know that "Accident Prone Aggression" should be split into "Accident Prone" and "Aggression", rather than "Accident" and "Prone Aggression"?

[HansV]

I edited Leesha's post to make it clearer what she meant.

[Leesha]

I tried this on a form and it ran perfectly! You have saved me from going blind and insane!
Thanks so much!!