Well, you can do it with formulas or with VBA. I think using a VBA solution is
easier to debug and support, so that's what I'll give you.
This routine uses Regular Expressions. It also assumes that your data types
(e.g. Symptom, Cause, etc) consist of a
space or beginning of line
Single Word
one or more spaces
Colon
one or more spaces
It also assumes that the groups of data always have the three (Symptom, Cause,
Rx) and always in that order.
The routine will take the line in the selected cell, and parse out the data
into the adjacent columns.
If it works with your real data, you should be able to modify it to step
through your entire data set.
But some debugging may be required.
To enter the routine, <alt-F11> opens the VB Editor. Ensure your project is
highlighted in the Project Explore window, then Insert/Module and paste the
code below into the window that opens.
Select Tools/References from the top menu and Select Microsoft VBScript Regular
Expressions 5.5 from the drop down list.
Then select a cell with the data in it. <alt-F8> opens the macro dialog box.
Select the macro and <RUN>.
Let's see what happens. It should handle multiple word descriptions, and as
many data sets as are in the cell.
If the quote marks are also in the cell, it will include the terminal quote,
but that's a simple fix, if that is the only quote mark in the cell.
=================================================
Option Explicit
Sub ParseData()
'Be sure to set Reference to Microsoft VBScript Regular Expressions 5.5
'See Tools/References on Main Menu Bar
Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection
Dim Match As Match
Dim Pattern As String
Dim Str As String
Dim c As Range
Dim i As Long 'counter
' Create a regular expression object.
Set objRegExp = New RegExp
objRegExp.Pattern = Pattern
' Set Case Insensitivity.
objRegExp.IgnoreCase = True
'Set global applicability.
objRegExp.Global = True
'set multiline
objRegExp.MultiLine = True
'Set Pattern to pick up data types
objRegExp.Pattern = "(\b\w+\s+:\s+)(.*?)(?=(\b\w+\s+:\s+)|$)"
For Each c In Selection 'set the range to parse here
Str = c.Text
'Test whether the String can be compared.
If (objRegExp.Test(Str) = True) Then
'Get the matches.
Set colMatches = objRegExp.Execute(Str) ' Execute search.
i = 1
For Each Match In colMatches
c.Offset(0, i).Value = Match.SubMatches(1)
i = i + 1
Next Match
End If
Next c
End Sub
=======================================
--ron
)