Excel - Paragraph of text in one column broken up into 4 columns

[teebird]

I have one column of text that I want to separate the text into 4 columns.

Column 1 contains all the text including the date
16/03/2012; Issues Identified: high blood pressure Referral/Action: Sent to GP Problem List Updated? No Recalls added? Yes new recall for BP

Column 1 Issues Identified:
high blood pressure

Column 2 Referral/Action:
Sent to GP

Column 3 Problem List Updated?
No

Column 4 Recalls added?
Yes new recall for BP

 

[SkipVought]

hi,

Unless this example is EXACTLY representative of all the other instances of text that you want to parse, you will have problems.

First you want to parse the data on the COLON, using Data > Text to columns and Do not import the first column.

Then using the resulting data in column 2, parse on QUESTION MARK.

That's probably the best you may get.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Deniall]

If the four text-strings / potential-column-headings "Issues Identified:", "Referral/Action:", "Problem List Updated?", and "Recalls added?" are always and exactly present in every record, you might be able to use four combinations of the FIND and MID functions to do the job. If there is a slight possibility that all four strings might not always be exactly present, then you might like to do a check first and correct any deficiencies (which will be easier than building "error checks" into your four formulae).

An alternative is VBA, to write yourself a function that will do the job. It can also do the "error checking". If you make it an "array function" you can extract and return all four bits from a single call to a single function.

Which approach you choose might be dictated by whether this is a "one-off" task, or something you will need to do multiple times as more of the inconveniently structured data comes in.

 

[Gruuuu]

teebird, will your data always be set up like this?:

[tt]
"[DATE DD/MM/YYYY]; Issues Identified: [TEXT] Referral/Action: [TEXT] Problem List Updated? [TEXT] Recalls added? [TEXT]"
[/tt]

Take note of the spaces, punctuation, and items inside the [SQUARE BRACKETS]. I placed items in [SQUARE BRACKETS] to represent changing data. If it's not in square brackets, take it to mean those characters exactly.

Please consider this carefully, since any help we give will be highly catered to how your data is set up, and we don't like solving the same problem multiple times

 

[teebird]

Many thanks everyone for your help with this.

@ Gruuuu
The data in the brackets changes but the spaces and headings eg. Issues Identified: remain the same.

Cheers Teebird

 

[Gruuuu]

Here's what I came up with

For posterity:
Columns are labeled

 A             B                       C                        D                      E              F
Date	Issues Identified:	Referral/Action:	Problem List Updated?	Recalls added?	Original Text

in the Date column[tt]
=LEFT($F2,SEARCH(";",$F2)-1)
[/tt]
(NOTE: This is not an actual date! It is for reading only! Changing it to a real date is more complicated, and we can help with that if you need it. If you are ONLY EVER GOING TO READ THIS, there's no need to convert. If, however, you wish to chart this or measure the frequency, it will need to be converted.)

in the Issues Identified: column[tt]
=MID($F2,SEARCH(B$1,$F2)+LEN(B$1)+1,SEARCH(C$1,$F2)-SEARCH(B$1,$F2)-LEN(B$1)-2)
[/tt]

in the Referral/Action: column[tt]
=MID($F2,SEARCH(C$1,$F2)+LEN(C$1)+1,SEARCH(D$1,$F2)-SEARCH(C$1,$F2)-LEN(C$1)-2)
[/tt]

in the Problem List Updated? column[tt]
=MID($F2,SEARCH(D$1,$F2)+LEN(D$1)+1,SEARCH(E$1,$F2)-SEARCH(D$1,$F2)-LEN(D$1)-2)
[/tt]

in the Recalls added? column[tt]
=RIGHT($F2,LEN($F2)-SEARCH(E$1,$F2)-LEN(E$1))
[/tt]

 

[teebird]

Hi Gruuuu

WOW excellent many thanks. I am not at work today but on Monday I will set up the columns with your code.
Really appreciate it - I did try but just do not have enough understanding of functions to make the logic work.
Again many thanks
Teebird.

 

[macropod]

As an alternative, you could use a macro like:
Sub ProcessData()
Application.ScreenUpdating = False
Dim LastRow As Long, i As Long, StrTxt As String, j As Long
With ActiveSheet
LastRow = .Range("A" & .Cells.SpecialCells(xlCellTypeLastCell).Row).End(xlUp).Row
For i = 2 To LastRow
StrTxt = .Cells(i, 1).Value
StrTxt = Right(StrTxt, Len(StrTxt) - InStr(StrTxt, ":"))
StrTxt = Replace(StrTxt, "Referral/Action:", vbTab)
StrTxt = Replace(StrTxt, "Problem List Updated?", vbTab)
StrTxt = Replace(StrTxt, "Recalls added?", vbTab)
For j = 0 To UBound(Split(StrTxt, vbTab))
Select Case j
Case 0: .Cells(i, 1).Value = Trim(Split(StrTxt, vbTab)(0))
Case 1: .Cells(i, 2).Value = Trim(Split(StrTxt, vbTab)(1))
Case 2: .Cells(i, 3).Value = Trim(Split(StrTxt, vbTab)(2))
Case 3: .Cells(i, 4).Value = Trim(Split(StrTxt, vbTab)(3))
End Select
Next
Next
End With
Application.ScreenUpdating = True
End Sub
The code assumes you have a heading row as row 1 and you want to start processing at row 2. If you don't have a heading row, or you want to start processing at some other row, change the '2' in 'For i = 2 To LastRow' to whatever row you want to start at.

Note: I haven't used code tags to preserve the code formatting as the new setup seems to be mangling code.

Cheers
Paul Edstein
[MS MVP - Word]