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
Splitting Info in a cell to separate rows
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
-
- Administrator
- Posts: 78494
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Splitting Info in a cell to separate rows
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.
Does that mean that it's actually a problem in another application (Access)? You posted the question in the Excel forum.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Splitting Info in a cell to separate rows
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!
Thanks!
-
- Administrator
- Posts: 78494
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Splitting Info in a cell to separate rows
Does the table contain other fields?
If so: what should happen to those fields? Should the values be repeated? Or ...?
If so: what should happen to those fields? Should the values be repeated? Or ...?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Splitting Info in a cell to separate rows
No it doesn't contain other fields. Just 2 columns.
-
- Administrator
- Posts: 78494
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Splitting Info in a cell to separate rows
Yes the first column is called "Symptom" and the Second is called "Emotional Routes". The data that is being split is in Emotional Routes.
-
- Administrator
- Posts: 78494
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Splitting Info in a cell to separate rows
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?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Splitting Info in a cell to separate rows
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.
Would result in below
Code: Select all
SYMPTOM EMOTIONAL ROUTE
Accident Prone Aggression, Fear of Speaking Out, Rebellion, Unprotected, Violence, Wrong
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
-
- Administrator
- Posts: 78494
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Splitting Info in a cell to separate rows
Here is code you can use:
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.
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
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 12609
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Splitting Info in a cell to separate rows
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"?
StuartR
-
- Administrator
- Posts: 78494
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Splitting Info in a cell to separate rows
I edited Leesha's post to make it clearer what she meant.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Splitting Info in a cell to separate rows
I tried this on a form and it ran perfectly! You have saved me from going blind and insane!
Thanks so much!!
Thanks so much!!