Turning a huge text list into an organized excel sheet - rowls, columns, ect.

**PatrickM** · 02-14-2012, 05:56 PM

I'm trying to turn an enormous contact list from a text/word/pdf format into a usable excel sheet. I'm having a big problem because what I need is something like conditionally transposed pasting... Not even sure where to start. Someone tell me if this is hopeless, because doing it manually will take so long that I'm going to have to keep trying till someone solves this or tells me to give up.

Basically I have a sheet that's:

Org name
Street Address
City, State Zip
(area) Phone-Number

and instead I need (humor me that "/" is a new cell column):

Org name / Address / City, State Zip / (area) Phone-Number

And then it gets even a little bit more complicated because there are randomly categories inserted occasionally categories before an org is listed, so sometimes it's:

Region 5
Org name
Street Address
City, State Zip
(area) Phone-Number

Uggg.

If it helps anything, I work at a health non-profit. We do good work, but could really use your help on this...

**AndyPS** · 02-14-2012, 06:13 PM

There are lots of ways of tackling this and I have done similar things many times. To help identify the best way, approximately how many lines of text are there?
The additional category identification will be a problem and there needs to be a set of rules to identify what is a caregory and how to distinguish it from the following organisation. If it always starts "Region" and no organisation is so named it will be easier!

**tigeravatar** · 02-14-2012, 06:14 PM

PatrickM,

Welcome to the forum!
Can you upload a sample of the text file so we can get a better idea of what it looks like and how to work with it? Just be sure to scrub sensitive data, or use mock data in the same format and layout.

**PatrickM** · 02-14-2012, 07:05 PM

Thanks for the quick replies. Our office internet dropped off, but it's back up (whew).

Here's a sample of data, I picked a few clinics that I thought wouldn't mind, but changed all their phone numbers to (888) 888-8888.

The category structure is Region->State->Type. There are 10 regions, about 4 types, and of course there are something like 55 state and US territories.
When I copy the data straight into excel, each line becomes 1 cell, all in one column. There are something like 24,000 cells to start out, so doing this manually would probably kill me.

One last note: while each clinic usually is 4 cells of data, occasionally there's an extra line for P.O. boxes. I don't need that data, but I point it out because the number of cells that should become each row of data isn't always the exact same.

Ok, here's a sample of my data...

REGION I
CONNECTICUT
GRANTEE(S)
FAIR HAVEN COMMUNITY HEALTH CENTER
374 GRAND AVENUE
NEW HAVEN, CT 06513
(888) 888-8888
CLINIC(S)
FAIR HAVEN COMMUNITY HEALTH CENTER FAMILY PLANNING CLINIC
374 GRAND AVENUE
NEW HAVEN, CT 06513
(888) 888-8888
SCHOOL BASED HEALTH CENTER FAIR HAVEN MIDDLE SCHOOL
164 GRAND AVENUE
NEW HAVEN, CT 06513
(888) 888-8888
DELEGATE(S)
HARTFORD PUBLIC SCHOOLS STUDENT HEALTH CENTERS
960 MAIN STREET
HARTFORD, CT 06103
(888) 888-8888
CLINIC(S)
FOX MIDDLE SCHOOL STUDENT HEALTH CENTER
305 GREENFIELD STREET
HARTFORD, CT 06112
(888) 888-8888
QUIRK MIDDLE SCHOOL STUDENT HEALTH CENTER
85 EDWARDS STREET
HARTFORD, CT 06120
(888) 888-8888
DELEGATE(S)
WINDHAM HIGH SBHC
355 HIGH STREET
WILLIMANTIC, CT 06226
(888) 888-8888
CLINIC(S)
WINDHAM HIGH SBHC
355 HIGH STREET
WILLIMANTIC, CT 06226
(860) 465-2465
WINDHAM MIDDLE SCHOOL WELLNESS CENTER
123 QUARRRY STREET
WILLIMANTIC, CT 06226
(860) 465-2465

MAINE
GRANTEE(S)
FAMILY PLANNING ASSOC. OF MAINE
43 GABRIEL DRIVE
AUGUSTA, ME 04330
(207) 622-7524
DELEGATE(S)
ACAPHS
169 ACADEMY STREET
PRESQUE ISLE, ME 04769
(207) 768-3056

**PatrickM** · 02-14-2012, 07:13 PM

Hey, my office shuts down at 6pm (10 minutes ago) and I've pushed it about as far as I can. I REALLY appreciate the help, and A, be back on this tomorrow morning, and B) following it from my phone tonight if anything posts. But I won't be able to post anything else at length till the morning.

AndyPS and tigeravatar, thank you so much for taking an interest in this!

**tigeravatar** · 02-14-2012, 07:17 PM

PatrickM,

So, just to be clear, using the data you provided, the output should look like the following, is that correct?

Org Name	Street Address	City, State Zip	(area) Phone-Number
FAIR HAVEN COMMUNITY HEALTH CENTER	374 GRAND AVENUE	NEW HAVEN, CT 06513	(888) 888-8888
FAIR HAVEN COMMUNITY HEALTH CENTER FAMILY PLANNING CLINIC	374 GRAND AVENUE	NEW HAVEN, CT 06513	(888) 888-8888
SCHOOL BASED HEALTH CENTER FAIR HAVEN MIDDLE SCHOOL	164 GRAND AVENUE	NEW HAVEN, CT 06513	(888) 888-8888
HARTFORD PUBLIC SCHOOLS STUDENT HEALTH CENTERS	960 MAIN STREET	HARTFORD, CT 06103	(888) 888-8888
FOX MIDDLE SCHOOL STUDENT HEALTH CENTER	305 GREENFIELD STREET	HARTFORD, CT 06112	(888) 888-8888
QUIRK MIDDLE SCHOOL STUDENT HEALTH CENTER	85 EDWARDS STREET	HARTFORD, CT 06120	(888) 888-8888
WINDHAM HIGH SBHC	355 HIGH STREET	WILLIMANTIC, CT 06226	(888) 888-8888
WINDHAM HIGH SBHC	355 HIGH STREET	WILLIMANTIC, CT 06226	(860) 465-2465
WINDHAM MIDDLE SCHOOL WELLNESS CENTER	123 QUARRRY STREET	WILLIMANTIC, CT 06226	(860) 465-2465
FAMILY PLANNING ASSOC. OF MAINE	43 GABRIEL DRIVE	AUGUSTA, ME 04330	(207) 622-7524
ACAPHS	169 ACADEMY STREET	PRESQUE ISLE, ME 04769	(207) 768-3056

**PatrickM** · 02-14-2012, 07:41 PM

Yes! That is it exactly. Thanks.

**AndyPS** · 02-15-2012, 02:26 PM

I think the main problem is going to be spotting the change from one record to another. Does the phone number -e.g. (888) 123-1234 - always come in the last line of the data list item? If so, is it of the same form?
Also, is this a once-off job? If so a small amount of manual manipulation is not too much of an overhead.
I would be quite tempted to do some data cleansing by

firstly importing the list into the first column of the worksheet (c. 24,000 rows)
next put numbers from 1 to 24,000 (equal to the row number) in an adjacent column
sort the two columns (the numbers preserve the original position)
find and delete the rows with words like 'Region' and the type (Grantee, Clinic)
if possible, delete the 'State' rows
sort the remaining rows back into the original number order

I have suggested this process as the human eye is very good at spotting the bits of heading you don't want but it can be quite defficult to define precisely all the rows to be discarded!
Unfortunately I haven't got time now to produce some code but will have a think about it and come back to you (if someone else hasn't get there first!).

**tigeravatar** · 02-15-2012, 03:11 PM

PatrickM,

Sorry for the delayed response. Attached is a test text file that contains the test data you provided. Also attached is an example workbook that can be used to import the text file information in the desired format. The 'Import Text File' button is mapped to this macro:

Sub tgr()
    
    Dim strTxtPath As String
    Dim arrLine() As String
    Dim arrData() As String
    Dim LineIndex As Long, DataIndex As Long, ColIndex As Integer
    Dim strTemp As String
    
    strTxtPath = Application.GetOpenFilename("Text Files, *.txt")
    If strTxtPath = "False" Then Exit Sub
    
    ActiveSheet.UsedRange.Offset(1).Clear
    ReDim arrLine(1 To Rows.Count)
    ReDim arrData(1 To 4, 1 To Rows.Count)
    
    Close #1
    Open strTxtPath For Input As #1
    Do While Not EOF(1)
        LineIndex = LineIndex + 1
        Line Input #1, arrLine(LineIndex)
        strTemp = Replace(Replace(Replace(Replace(arrLine(LineIndex), "(", ""), ")", ""), " ", ""), "-", "")
        If Len(strTemp) = 10 And IsNumeric(strTemp) Then
            DataIndex = DataIndex + 1
            arrData(1, DataIndex) = arrLine(LineIndex - 3)
            arrData(2, DataIndex) = arrLine(LineIndex - 2)
            arrData(3, DataIndex) = arrLine(LineIndex - 1)
            arrData(4, DataIndex) = arrLine(LineIndex - 0)
            LineIndex = 0
        End If
    Loop
    Close #1
    
    If DataIndex > 0 Then
        ReDim Preserve arrData(1 To 4, 1 To DataIndex)
        Range("A2:D2").Resize(DataIndex).Value = Application.Transpose(arrData)
    End If
    
    Erase arrData
    Erase arrLine
    
End Sub

Just in case you are new to macros:
How to use a macro:

Make a copy of the workbook the macro will be run on
- Always run new code on a workbook copy, just in case the code doesn't run smoothly
- This is especially true of any code that deletes anything
In the copied workbook, press ALT+F11 to open the Visual Basic Editor
Insert | Module
Copy the provided code and paste into the module
Close the Visual Basic Editor
In Excel, press ALT+F8 to bring up the list of available macros to run
Double-click the desired macro (I named this one tgr)

Turning a huge text list into an organized excel sheet - rowls, columns, ect.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Turning a huge text list into an organized excel sheet - rowls, columns, ect.

Re: Turning a huge text list into an organized excel sheet - rowls, columns, ect.

Re: Turning a huge text list into an organized excel sheet - rowls, columns, ect.

Re: Turning a huge text list into an organized excel sheet - rowls, columns, ect.

Re: Turning a huge text list into an organized excel sheet - rowls, columns, ect.

Re: Turning a huge text list into an organized excel sheet - rowls, columns, ect.

Re: Turning a huge text list into an organized excel sheet - rowls, columns, ect.

Re: Turning a huge text list into an organized excel sheet - rowls, columns, ect.

Re: Turning a huge text list into an organized excel sheet - rowls, columns, ect.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions