Excel 2000 - one column into 5 columns 5

ddamm · Aug 13, 2003

We have a mail list of about 2500 addresses that is in a single column with blank lines as shown below. We need to convert this list into at least 5 separate columns for name, address, city, state, and zip so that our mailing software can work with the list. The city, state and zip information occupies a single cell. Any ideas on the best way to do this?

blank line
name 1
blank line
address 1
city state zip
blank line
blank line
name 2
blank line
address 2
city state zip
blank line

carrr · Aug 13, 2003

Data > Text to Columns > follow the wizard, spec out where your breaks fall...

SkipVought · Aug 13, 2003

Hi,

This should work

Code:

Sub ToColumns()
    Dim iCol As Byte, lRow As Long
    iCol = 2
    lRow = 2
    For Each c In Selection
        With c
            If .Value <> &quot;&quot; Then
                Select Case iCol
                    Case 2, 3
                        Cells(lRow, iCol).Value = c.Value
                    Case 4
                        sValue = &quot;&quot;
                        j = 2
                        For i = Len(c.Value) To 1 Step -1
                            sByte = Mid(c.Value, i, 1)
                            If sByte = &quot; &quot; Then
                                Cells(lRow, iCol + j).Value = sValue
                                sValue = &quot;&quot;
                                j = j - 1
                            Else
                                sValue = sByte & sValue
                            End If
                            If j < 0 Then Exit For
                        Next
                        Cells(lRow, iCol).Value = sValue
                End Select
                iCol = iCol + 1
            End If
        End With
        If iCol > 4 Then
            iCol = 2
            lRow = lRow + 1
        End If
    Next
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

SkipVought · Aug 13, 2003

ddamm,

You know, parsing city state zip cannot work predicatable WITHOUT a unique delimiter because you have such cases as...

Code:

Elmyra New York 99999
North Huntington Rhode Island 99999
West El Paso Texas 99999

I jumped to a conculsion with my code and it will not work in all cases!!!!!

[blush]

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

ddamm · Aug 13, 2003

That didn't quite work. Using "space" as a delimiter, the name was on one row, address on the second, and city state zip on the third. The list did break into 3 columns. So, that's a step in the right direction. Using "tab" as a delimiter didn't work - the list stayed in a single column. I am being told that our software will parse the city state and zip if we cannot separate it. So we could work with 3 columns (name, address, city-state-zip) instead of 5.

carrr · Aug 13, 2003

You know that if you choose "fixed width" you can point and click, inserting breaks as you dictate, in the next step of the wizard, right? So, if you have a standard gap between elements, this should work...'course I'm making a lot of guesses about your data structure....

SkipVought · Aug 13, 2003

Did you copy and paste my code.

Each record is placed in a single row.

To get everyting into 3 columns just change

Code:

 Case 2, 3

to

Code:

 Case 2, 3, 4

and comment out Case 4

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

knitwit · Aug 13, 2003

If you need to get rid of the blank rows, fill a column with ascending numbers. Sort your data by col. a (where your name, address, etc. is located), then highlight the actual data and the numbers and resort by the numbers - then you'll just have your data to tweak.

bluedragon2 · Aug 13, 2003

Just because I like making formulas:

in B1, put =IF(ROW(A1)-(INT(ROW(A1)/6)*6)=1,A2,"&quot

and then copy and paste in down.

in C1, put =IF(ROW(A1)-(INT(ROW(A1)/6)*6)=1,A4,"&quot

and copy and paste down.

in d1, put =IF(ROW(A1)-(INT(ROW(A1)/6)*6)=1,A5,"&quot

and then copy and paste down.

Blue

mscallisto · Aug 13, 2003

very clever bluedragon2

bluedragon2 · Aug 13, 2003

I blush

Hey ddamm, give me an example of one of your city/st/zip's

Blue

ddamm · Aug 13, 2003

Carrr,
All the info is in column A, so cannot snap a line without going through other information. Would have been a simple solution.

Skip,
Tried the original macro. Lines 1-80 parsed correctly. Then the next record starts in column 3 and continues like that for a while. Then the first name starts to appear in various places such that the records 'wrap' from one line to the next. It ran like that until line 1920. All of the records after that are untouched.
I Remed out case 4, but it doesn't seem to be working correctly. Must have missed something.

Blue Dragon,
Slick formula! Works! mscallisto is right. Very cute.
An example of a record:
Blank line
Susan Doe
Blank line
5605 Elizabeth Loop NE
Auburn, WA 98092
Blank line

Thanks,
David

SkipVought · Aug 13, 2003

Does the pattern change at line 81?

You might have to modify the code if there are SPACE characters in those blank rows...

Code:

If Trim(.Value) <> &quot;&quot; Then

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

bluedragon2 · Aug 14, 2003

With you data sampl, I will assume that the zip is always 5 digits and the city and state are divided by a ,:

City:

=LEFT(D1,FIND(",",D1)-1)

State:

=MID(D1,FIND(",",D1)+2,LEN(D1)-FIND(",",D1)-6)

Zip:

=RIGHT(D1,5)

This can be modified for 9 digit zips, or a combonation of both...

Blue

SkipVought · Aug 14, 2003

But if there are Canadian or other postal codes, or 99999-9999 US ZIP codes, that assumption will not work either!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

bluedragon2 · Aug 14, 2003

You are right Skip, that is why I said, if you have 9 digit zips or such, it has to be modified to allow for them...

Blue

ddamm · Aug 14, 2003

Skip,
The change at line 81 is due to the fact that the record is missing the street address (it only has the name, city-state-zip).

Blue,
Some of the zip codes are 9 digits (99999-9999), but they are few and far between. With Skip's code, they break into two columns. The city is always separated by a comma from the state.

This list doesn't have any Canadian addresses, but we do a number of mailings that do. I troubleshoot the mailing software once in a while, but I am not the guy that runs it.

To modify the VB code for 9 digits, would I change the line to =RIGHT(D1,9)?

Thanks for the suggestions,

David

ddamm · Aug 14, 2003

Hey guys,

Don't sweat the Candians. I think we can have the mailing software kick them out and deal with them separately.

David

SkipVought · Aug 14, 2003

Well, guess what!

Garbage in (data not according to your specification)
Garbage out!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

bluedragon2 · Aug 14, 2003

ddamm, You would have:

=IF(MID(D1,LEN(D1)-4,1)="-",MID(D1,FIND(",",D1)+2,LEN(D1)-FIND(",",D1)-11),MID(D1,FIND(",",D1)+2,LEN(D1)-FIND(",",D1)-6))

for the state, and

=IF(MID(D1,LEN(D1)-4,1)="-",RIGHT(D1,10),RIGHT(D1,5))

for the zip

Blue

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Excel 2000 - one column into 5 columns 5

IS-IT--Management

Technical User

Programmer

Programmer

IS-IT--Management

Technical User

Programmer

Technical User

IS-IT--Management

Technical User

IS-IT--Management

IS-IT--Management

Programmer

IS-IT--Management

Programmer

IS-IT--Management

IS-IT--Management

IS-IT--Management

Programmer

IS-IT--Management

Similar threads

Log in

Part and Inventory Search

Sponsor