Thread: Mail Merge Help

If you have an [enter] at the end of each line as you say you do, you can just have it parse the addresses line by line every time it finds the [enter] character in the address, which is CHAR(10). So the first line would be given by:


=IF(COLUMNS($B:B)=1,MID($A1,1,FIND(CHAR(10),$A1,1)-1),"hmm... maybe a macro would be better?")

OK, a simple macro would accomplish this; if no one else jumps in, I will write one shortly when I have time.

OK here's a very primitive macro (all I'm capable of with VBA) that seems to work. In case you've never used macros before, I will go through this step-by-step.

Put your addresses in Col A starting at row 2 (if you can't move them for some reason, let me know); so first address should be in cell A2.

In cell A1, type (if you can't use cell A1, let me know):
=CHAR(10)

Make sure there's always only ONE blank cell between addresses.

Go to Tools Menu --> Macros... --> Record New Macro

Enter a letter to use as a shortcut (let's say, "n") in the box.

Click OK (a small macro control box will pop up)

Click the square button on the macro control box to stop recording (we're not really recording anything-- this is just an easy way to create a shortcut for the macro we're about to paste in).

Back to Tools Menu --> Macros... --> Macro --> Edit

This brings up the VBA editor.

Just above the last line of code (which should be End Sub), paste this:

Code:

tmm = 0
rm = 2

Do
    am = 1
    tm = 0

    Do
        If Mid(Cells(rm, 1), am, 1) = Cells(1, 1) Then tm = tm + 1
        am = am + 1
        tmm = WorksheetFunction.Max(tmm, tm)

    Loop Until am > Len(Cells(rm, 1))
    rm = rm + 2

Loop Until Cells(rm, 1) = ""

r = 2

Do

    If Cells(r, 1) = "" Then
        r = r + 1
        If Cells(r, 1) = "" Then GoTo endaddy
    End If

    t = 0
    a = 1

    Do

        If Mid(Cells(r, 1), a, 1) = Cells(1, 1) Then t = t + 1
        a = a + 1

    Loop Until a > Len(Cells(r, 1))

    y = 1
    c = 2
    u = 1

    Do

        y = WorksheetFunction.Find(Cells(1, 1), Cells(r, 1), y)
        
        If u = 1 Then
            Cells(r, c) = Left(Cells(r, 1), y - 1)
        Else
            Cells(r, c) = Mid(Cells(r, 1), y + 1, _
                WorksheetFunction.Find(Cells(1, 1), Cells(r, 1), y + 1) - (y + 1))
            y = y + 1
        End If

        c = c + 1
        u = u + 1
 'NOTE: If ANY addresses contain a line AFTER city/state/zip, comment-out the following
 'one line of code (If u = t Then...) by putting an apostrophe (') in front of it
        
        If u = t Then c = tmm + 1
       
    Loop Until u > t

    r = r + 1

Loop

endaddy:

'END OF COPY AREA; End Sub should come immediately after this line...

Now, go back to your Excel sheet and hit Cntrl-n to run the macro

Let me know if it doesn't work as expected.

I am about to use this now, but how do I make sure there is only one blank cell between each address?

I know how to select and show all blank cells, but is there a way on deleting consecutive ones and keeping just one between each adress?

I really do appreciate all your help! once I get the blanks down to one each, I will try the macro and let u know !

Not a problem-- I was just using the appearance of two consecutive blank cells as a signal to stop the program. I changed the code a bit (below), so now it won't stop until it finds 50 consecutive blank cells (I'm sure there are way more efficient ways of doing this, but I don't know them).

Code:

'NOTES:  This program parses mailing addresses that are stored in a single cell
    'with carriage returns separating each line (name/street addr/city,state zip)
    'It assumes that the LAST LINE in all addresses contains the same
    'type of information, for instance, city,state zip OR country etc.
    'If this is not the case (e.g. some have city,state zip as last line
    'and some have country as last line), comment-out the line of code
    'that's indicated with a NOTE near the end of the program.  Note that doing
    'so may cause the column in which certain address information appears
    'to be inconsistent with others, e.g., city, state zip may appear in Col D
    'for some and in Col E for others.
    'The program also assumes that all addresses start with the same type of info,
    'for instance, name, and that the second line is also of the same type, e.g.
    'first line of street address.  Multiple street address lines for some and
    'not for others IS OK, however.
    
    'The program requires that the addresses appear in Column A beginning at least the
    'second row down (i.e. A2 or below), that not more than 50 blank cells
    'separate any two addresses, and that the carriage return character
    '(Excel character #10, which is invisible) is present in cell A1-- it will
    'now put this formula in automatically.
    
'BEGIN PROGRAM

Cells(1, 1) = "=CHAR(10)"
Cells(1, 1).Interior.Color = vbYellow

'FINDING MAX NUMBER OF ADDRESS LINES
tmm = 0
rm = 2

Do
    am = 1
    tm = 0

    Do
        If Mid(Cells(rm, 1), am, 1) = Cells(1, 1) Then tm = tm + 1
        am = am + 1
        tmm = WorksheetFunction.Max(tmm, tm)

    Loop Until am > Len(Cells(rm, 1))
    rm = rm + 2

Loop Until Cells(rm, 1) = ""

'BEGIN ADDRESS PARSING
r = 2

Do
    'CHECKING FOR END OF DATASET
    e = 0
    Do
        If Len(Cells(r, 1)) = 0 Then
            r = r + 1
            e = e + 1
        End If
    Loop Until Len(Cells(r, 1)) > 0 Or e >= 50
    'ALLOWING ESCAPE FROM PROGRAM
    If e >= 50 Then GoTo endaddy
    
    'CONTINUING PROGRAM

    'COUNTING NUMBER OF RETURNS (EXCEL CHARACTER #10) IN CURRENT ADDRESS ROW
    t = 0
    a = 1
    
    Do

        If Mid(Cells(r, 1), a, 1) = Cells(1, 1) Then t = t + 1
        a = a + 1

    Loop Until a > Len(Cells(r, 1))
    If t = 0 Then
        Cells(r, 2) = "<<==NO CARRIAGE RETURNS FOUND!"
        Cells(r, 2).Interior.Color = vbYellow
        GoTo nextaddy
    End If
    
    
    'PARSING ADDRESS LINES INTO COLUMNS
    y = 1
    c = 2
    u = 1

    Do

        y = WorksheetFunction.Find(Cells(1, 1), Cells(r, 1), y)
        
        If u = 1 Then
            Cells(r, c) = Left(Cells(r, 1), y - 1)
        Else
            Cells(r, c) = Mid(Cells(r, 1), y + 1, _
                WorksheetFunction.Find(Cells(1, 1), Cells(r, 1), y + 1) - (y + 1))
            y = y + 1
        End If

        c = c + 1
        u = u + 1
 'NOTE: If ANY addresses contain a line AFTER city/state/zip, comment-out the following
 'one line of code (If u = t Then...) by putting an apostrophe (') in front of it
        
        If u = t Then c = tmm + 1
       
    Loop Until u > t

nextaddy:
    
    r = r + 1

Loop

'STOPPING ADDRESS PARSER
endaddy:

Cells.Select
Cells.EntireColumn.AutoFit

End Sub

Note that the code now includes the last line End Sub, so delete the one that's already in your code window before pasting the above in. I added some notes and some comments to show what the code is doing. It's also a little more flexible and intelligent, I hope.

Hope that works for you!

Okay I tried it with the new Macro.

I followed all steps, and when I click ctrl n. I get a new excel sheet that is blank named Book1.

I tried it a few times, yet still got the blank sheet.

any suggestions?

Wow, it never occurred to me that would happen. Apparently, Excel is not letting you use Cntrl-n as a macro shortcut because it's already reserved Cntrl-n for "create new workbook".
I assume you chose "n" as your shortcut letter when you went through the "Record New Macro..." step?? At the top of your code (before the portion that you pasted in from here) it should tell you what shortcut it has associated with it-- does it say Cntrl-n?

If it's really just not letting you use "n", try it with a different letter-- I would suggest "k" or "i" -- I don't think those are reserved. Funny-- I never have this problem.

In any case, the code should work (it works fine on mine): I am attaching a workbook with identical code so you can see. Let me know! I will make the shortcut Cntrl-k. You will have to have macros enabled (it should ask when you try opening it). If you're not comfortable doing this, I would suggest trying yours again with "k" as the shortcut key-- note that you will have to do the Record New Macro thing again; you can't simply change the comment at the top of your code to say Cntrl-k.

Assuming you use the workbook I'm attaching (and that it works as it did for me), notice that it didn't handle one of the addresses properly because one of them had two "name" lines and the street address doesn't start til row 3 (I just used some of the addresses you posted originally). Not sure what to do about that. Take a look.

I made a tiny change to the code that's in the attachment above. The new code is as follows (the only change is in the section called FINDING MAX NUMBER OF ADDRESS LINES):

Code:

'BEGIN PROGRAM

Cells(1, 1) = "=CHAR(10)"
Cells(1, 1).Interior.Color = vbYellow

'FINDING MAX NUMBER OF ADDRESS LINES
tmm = 0
rm = 2

Do
    am = 1
    tm = 0
    em = 0
    Do
        If Len(Cells(rm, 1)) > 0 Then
            
            If Mid(Cells(rm, 1), am, 1) = Cells(1, 1) Then tm = tm + 1
            am = am + 1
            tmm = WorksheetFunction.Max(tmm, tm)
        Else
            rm = rm + 1
            em = em + 1
        End If
    Loop Until am > Len(Cells(rm, 1)) + 1 Or em >= 50
    If em >= 50 Then GoTo startparse
    rm = rm + 1

Loop

'BEGIN ADDRESS PARSING
startparse:
r = 2

Do
    'CHECKING FOR END OF DATASET
    e = 0
    Do
        If Len(Cells(r, 1)) = 0 Then
            r = r + 1
            e = e + 1
        End If
    Loop Until Len(Cells(r, 1)) > 0 Or e >= 50
    'ALLOWING ESCAPE FROM PROGRAM
    If e >= 50 Then GoTo endaddy
    
    'CONTINUING PROGRAM

    'COUNTING NUMBER OF RETURNS (EXCEL CHARACTER #10) IN CURRENT ADDRESS ROW
    t = 0
    a = 1
    
    Do

        If Mid(Cells(r, 1), a, 1) = Cells(1, 1) Then t = t + 1
        a = a + 1

    Loop Until a > Len(Cells(r, 1))
    If t = 0 Then
        Cells(r, 2) = "<<==NO CARRIAGE RETURNS FOUND!"
        Cells(r, 2).Interior.Color = vbYellow
        GoTo nextaddy
    End If
    
    
    'PARSING ADDRESS LINES INTO COLUMNS
    y = 1
    c = 2
    u = 1

    Do

        y = WorksheetFunction.Find(Cells(1, 1), Cells(r, 1), y)
        
        If u = 1 Then
            Cells(r, c) = Left(Cells(r, 1), y - 1)
        Else
            Cells(r, c) = Mid(Cells(r, 1), y + 1, _
                WorksheetFunction.Find(Cells(1, 1), Cells(r, 1), y + 1) - (y + 1))
            y = y + 1
        End If

        c = c + 1
        u = u + 1
 'NOTE: If ANY addresses contain a line AFTER city/state/zip, comment-out the following
 'one line of code (If u = t Then...) by putting an apostrophe (') in front of it
        
        If u = t Then c = tmm + 1
       
    Loop Until u > t

nextaddy:
    
    r = r + 1

Loop

'STOPPING ADDRESS PARSER
endaddy:

Columns("B:IV").Select
Columns("B:IV").EntireColumn.AutoFit

End Sub

The sameple you added looks good! but for some reason I tried both K and i and they didn't work. K tried opening a hyperlink....

as far as what it says on the top of the macro :

Sub i()
'
' i Macro
' Macro recorded 1/8/2009 by Andy Najjar
'


So does this mean the shortcut is sub i() ?


and when I went to Macro and clicked run this is what I got, not sure if I should ahve done that, but I tried it to see if it was just the problem of running it....



"
"
Quality Department <<==NO CARRIAGE RETURNS FOUND!
A.E. Petsche Company, Inc. <<==NO CARRIAGE RETURNS FOUND!
1785 Corporate Drive, #610 <<==NO CARRIAGE RETURNS FOUND!
Norcross, Georgia 30093 <<==NO CARRIAGE RETURNS FOUND!

Quality Department <<==NO CARRIAGE RETURNS FOUND!
A.S.A.P. Technologies, Inc. <<==NO CARRIAGE RETURNS FOUND!
1835 South Lee Court <<==NO CARRIAGE RETURNS FOUND!
Buford, Georgia 30518 <<==NO CARRIAGE RETURNS FOUND!

Quality Department <<==NO CARRIAGE RETURNS FOUND!
AAR Defense Systems & Logistics <<==NO CARRIAGE RETURNS FOUND!
3240 Avondale Mill Road <<==NO CARRIAGE RETURNS FOUND!
Macon, Georgia 31216 <<==NO CARRIAGE RETURNS FOUND!

Quality Department <<==NO CARRIAGE RETURNS FOUND!
ABB Inc. <<==NO CARRIAGE RETURNS FOUND!
1955 Evergreen Boulevard <<==NO CARRIAGE RETURNS FOUND!
Suite 100 <<==NO CARRIAGE RETURNS FOUND!
Duluth, Georgia 30096-1207 <<==NO CARRIAGE RETURNS FOUND!

Victoria Atkins <<==NO CARRIAGE RETURNS FOUND!
ACE Marketing Services <<==NO CARRIAGE RETURNS FOUND!
1961 South Cobb Industrial <<==NO CARRIAGE RETURNS FOUND!
Smyrna, Georgia 30082 <<==NO CARRIAGE RETURNS FOUND!

Mr. Joel Blackburn <<==NO CARRIAGE RETURNS FOUND!
Sales Tech Services <<==NO CARRIAGE RETURNS FOUND!
ACT Technologies, Incorporated <<==NO CARRIAGE RETURNS FOUND!
1800 Kimberly Park Drive <<==NO CARRIAGE RETURNS FOUND!
Dalton, Georgia 30722- <<==NO CARRIAGE RETURNS FOUND!

Alan Costello <<==NO CARRIAGE RETURNS FOUND!
Action Electrical & Mechanical Contractors <<==NO CARRIAGE RETURNS FOUND!
340 Henry Ford II Avenue, <<==NO CARRIAGE RETURNS FOUND!
Hapeville, Georgia 30354 <<==NO CARRIAGE RETURNS FOUND!

yeah, I don't know why the moderator locked my original post.... its for the similar problem, but i want to do two different things.....

and yes every cell has a single thing. Name in one cell, address in another, etc..

they are not all in the same cell.... I appologise for the misunderstanding. I hope you can still help me!

Im trying to get this fixed before next week.

i kinda wish I could get some more replies from other users also........ I don't want u to be doing all the work lol

OK, I think the attached will do what you need. All you need to do is paste your addresses in Column E below the green header and your results will appear to the right under the blue headers. If the formulas in columns A:D and/or F:N do not extend as far down the sheet as your pasted addresses, a warning will appear in cell B1 telling you this.

I would strongly advise that you save an extra blank copy of this if you're going to be using it more than once, and I would also strongly advise that you not alter ANYTHING on the spreadsheet (other than to add new addresses, fill-down formulas, or alter width/height of columns and rows). Unless you really know what you're doing, altering any column headers or formulas will cause all sorts of problems. Many of the column headers, by the way, are actually created by formulas (they will change depending on the dataset), so be aware. You may encounter problems if any of your addresses have a first row of the STREET-portion of the address that does NOT contain any numbers.

Good luck. Hope it works for you....

This works great ! Thank you so much !

The formulas do however cut short from my list. My list goes to line 5833. Do I paste it into this spreadsheet in portions to get the list done?

or am I amble to extend and have the formulas continue farther ?

Well now I am able to mail merge this list with the help of the formula table done by clownfish.....

I wasn't able to do that first because they were all in one row and each line was in a seperate cell.