Convert lists of text in Word to excel

Grosspsych · #1 01-30-2012, 09:48 AM

I am trying to figure our how to convert a list of repeating categories with text after each to a spreadsheet. I copied the format below and want the bold categories in each line (which repeat in each block down the list) to become column headers and the text after the colon for each category to go as a row under the correct column header. Any ideas? Thanks

Category: Administrative
Issue: Information Exchange
Description: It would be desirable to have electronic health information exchange between community hospitals and the correctional facilities.
Impact: Continuity of Care
Departments Affected: All

Scope of Problem: Widespread
Severity: Medium
Possible Solutions: State to consider options for electronic health information exchange.
__________________________________________________ ___________________________________
Category:
Issue:
Description:
Impact:
Departments Affected:
Scope of Problem:
Severity:
Possible Solutions:

Catalin.B · #2 01-31-2012, 01:45 AM

You can try the code:

Code:

Option Explicit
Sub test()
Dim LastRow As Long, i, j As Integer, Val As String
With Sheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
j = 2
For i = 1 To LastRow
Val = .Cells(i, 1)
.Cells(1, j) = Left(Val, Application.Find(":", Val, 1))
.Cells(2, j) = Right(Val, Len(Val) - Application.Find(":", Val, 1))
j = j + 1
Next i
.Columns("A:A").Delete
End With
End Sub

If you find them useful, the formulas used in the macro are in the file attached

Grosspsych · #3 01-31-2012, 07:02 AM

Thanks so much for taking the time to do that. It works perfectly for one block of text. When I tried to copy the multiple blocks of text into the spreadsheet and run the macro, it would not work, as I think it was meant only for one group of text items. I am going to have about 200-300 groups of items like this with the same headers.

Catalin.B · #4 01-31-2012, 07:17 AM

You need to adjust the macro to how your data is organised.
What do you mean by blocks of text? How long they are, is a one column range, ...
You have to give more details for a better solution, maybe uploading a sample with your data structure is not a bad idea.

Grosspsych · #5 01-31-2012, 07:32 AM

I am sorry for being unclear and am ignorant about how to adjust macros, unfortunately. When I say blocks of text, I mean the grouping of eight categories that I want to go as column headers in Excel and the text after each of them, that goes below each of them in the spreadsheet - each group of eight I am calling a block of text. I will have many of these to convert to a spreadsheet with one row of headers and the data for each header below it in the spreadsheet. I am pasting 3 "blocks of text" below as a more extensive example of what I have in Word. I only need the bold category headers to be in the first row of Excel as column headers and the text after each colon to go sequentially below the appropriate header.

Example:

Category: Modules
Issue: Scheduling
Description: EPM has not been utilized thus all departments are using scheduling in manual ways or using Excel, Access, and/or corporate-driven systems. This does not link in with clinical encounters, creates inefficiency, limits reporting capabilities, and makes it impossible for departments to see appointments for the same patient across multiple departments.
Impact: Productivity, Continuity of Care, Quality of Care
Departments Affected: All
Scope of Problem: Widespread
Severity: Very High
Possible Solutions: EPM to be installed and staff trained on how to set it up and utilize it.
__________________________________________________ __________________________________
Category: Modules
Issue: Reporting
Description: There are very few preloaded Crystal reports. Staff has not been trained on how to create reports using the NextGen reporting module and many complaints that it is difficult and not user-friendly. Feedback suggests there is a strong desire/need for multiple reports to be prepopulated for statistical means, tracking, and compliance with state and accreditation standards.
Impact: Productivity/Efficiency, Quality of Care
Departments Affected: All
Scope of Problem: Widespread
Severity: High
Possible Solutions: Departments to provide copies of reports that are needed with variables needed. Reports can be pre-programmed for simple access and easy reporting.
__________________________________________________ __________________________________
Category: Modules
Issue: Chart Organization
Description: Widespread complaints that using the history view is very difficult to use, especially with inmates who have been in the system for some time. There are too few documents types to distinguish between documents (e.g., multiple types of notes across mental health and psychiatry print as BH_Master). There is little to no ability to filter or search by type of note or provider name. Sometimes staff is not able to find what they are looking for in a reasonable amount of time.
Impact: Productivity/Efficiency, Patient Safety, Continuity of Care
Departments Affected: All
Scope of Problem: Widespread
Severity: High
Possible Solutions: Documents within disciplines need to have a sufficient variability in name to be able to differentiate exactly what type of document it is. Category view is currently poorly organized and needs to be reconfigured to allow staff to more efficiently search for documents by category and date.

macropod · #6 02-06-2012, 08:03 PM

Hi Grosspsych,

See attached. Your data go on Sheet1 and the formulae on Sheet2 parse the data. Simply copy the formulae on Sheet2 down as far as needed.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How can I convert pptx to text format like Word, and later on create pptx too?	GetLost	PowerPoint	0	01-11-2012 04:23 AM
My plain text post got converted to rich text in a reply, how to convert it back?	david.karr	Outlook	0	01-05-2012 09:46 AM
Drop-down lists in Excel	Maureen	Excel	1	11-10-2011 08:11 AM
Renumbering lists for hidden text?	christie	Word	1	09-04-2011 05:21 AM
XML List of lists in Excel or Calc	manofspider	Excel	1	08-05-2011 09:03 AM