Words apportionment of an adress

[newvbie]

Hi there. I've got 3 cells in an excel worksheet containing 3 parts of a full address. Now what I want is first merge these 3 cells into 1 and then, to fit the letter head printed with address (extracted from the worksheet) to the open-window envelop, the address is to be split into 3 line (3 cells again) each containing about 15 words. As I got 12,000 adresses on the list, can this be done by vb?

1. Merged into 1 cell
A1 & " " & A2 & " " & A3

2. split again into 3 cells each containing about 15 words (space at the end of a completed or hyphenated word.

Any advice on the step 2 please.

Thanks.

 

[energman58]

15 words or 15 characters? 15 words * 3 sounds like a very long address unless it is of the form
"past the house with a red roof, there used to be a shop with a
blue front there, take the way on the left where there is often a black
cat - our house is the last in the road with a yellow sports car outside"!

What are the exact limitations and rules for your final address - "about 15" isnt really going to be very easy to code unless you define what it means.

 

[newvbie]

Hi there. Sorry for the confusion. What I meant is each cell, after the split, should contains about 15-20 characters. Please refer to the example as follows:

146 Hatfield Avenue, Campus Tower, 5/F, Hertfordshire, United Kingdom.

The above address should be split into 3 cells:

cell A1 146 hatfield Avenue,
Cell B1 Campus Tower, 5/F
Cell C1 Hertfordshire, United Kingdom

Thanks.

 

[energman58]

Hi

I did it with a formula solution using a helper cell to make the formulas more manageable - you can do it without if you use 2007 or 2010 which allow longer formulas but debugging the formula will be a nightmare. Based on your example I have assumed that you can break the address at any comma followed by a space - if you want something different (for example breaking at spaces) you can probably adjust my formulas.

You could also do the same in VBA if you want

In the following it assumes your original long string is in A1

B4 contains the maximum lenth of your line (I used 22 characters)
D4 contains the shortest possible line (I used 15 characters)

A6, A7 and A8 contain the 3 lines to print - the final line may be longer than the maximum and includes any text that isnt in the lines above

A10 contains my helper formula

These are the formulas:

In A6

=LEFT(A1,IF(FIND(", ",A1)<$D$4,IF(FIND(", ",A1,1+FIND(", ",A1))>$B$4,$B$4-1,FIND(", ",A1,1+FIND(", ",A1))),IF(FIND(", ",A1)>$B$4,$B$4-1,FIND(", ",A1))))&IF(FIND(", ",A1)>$B$4,"-",IF(AND((FIND(", ",A1)<$D$4),(FIND(", ",A1,1+FIND(", ",A1))>$B$4)),"-",""))

IN A7

=TRIM(LEFT(A10,IF(FIND(", ",A10)<$D$4,IF(FIND(", ",A10,1+FIND(", ",A10))>$B$4,$B$4-1,FIND(", ",A10,1+FIND(", ",A10))),IF(FIND(", ",A10)>$B$4,$B$4-1,FIND(", ",A10))))&IF(FIND(", ",A10)>$B$4,"-",IF(AND((FIND(", ",A10)<$D$4),(FIND(", ",A10,1+FIND(", ",A10))>$B$4)),"-","")))

In A8

=TRIM(SUBSTITUTE(A10,A7,""))

In A10 - my helper formula

=SUBSTITUTE(A1,LEFT(A1,IF(FIND(", ",A1)<$D$4,IF(FIND(", ",A1,1+FIND(", ",A1))>$B$4,$B$4-1,FIND(", ",A1,1+FIND(", ",A1))),IF(FIND(", ",A1)>$B$4,$B$4-1,FIND(", ",A1)))),"")

There is probably a slicker way to do this but it works!

The result from your example is:

146, Hatfield Avenue,
Campus Tower, 5/F,
Hertfordshire, United Kingdom

BTW - here in the sandpit you get addresses like my example but mail only gets delivered to PO Boxes for obvious reasons!

PS - if you change my formulas note that FIND and SEARCH are more or less the same function but one is case sensitive (I cant remember which one and it doesnt matter if you are looking for commas or spaces) so you may have to change FIND for SEARCH depending on what breaking character you use - check the help

 

[newvbie]

Hi, energman58. Thanks a lot for your formula. Indeed, it looks a bit complicated to me. I'll study your formula and see if I can modify a bit to suit my case. Again, thanks a lot.