Microsoft Office Forums - View Single Post

BobBridges · 07-31-2013, 09:08 AM

On your phone? Cool; it's never occurred to me to try a forum like this on my phone.

Anyway, you didn't say whether you'd prefer a VBA or a formula solution. Let's go with formulae first, because most people are afraid of VBA and also because you asked on the Excel forum rather than on the Excel-Programming forum. (But if you want to try VBA, let me know).

It seems to me this can work with a lot of FIND and MID functions. Let's presume that the real address in your worksheet looks like this:

Code:

Calvary Chapel
Ellensburg 1.9 miles
840 Cowboy Lane
Ellensburg, Washington 98926
Phone: 509-925-1651
Email:taddscheffer@gmail.com
Visit Website Get Directions

The MID function can pick out a piece of a character string; for example, if your sample address were in $A$2 then this function would display just the telephone number:

Code:

=MID($A$2,89,12)

That is, it'll pick out a character string starting at position 89 and going on for 12 characters: "509-925-1651".

But of course the phone number starts at different positions in each cell of your list. So you use the FIND function to get those positions. For this example I used "=FIND("Phone:",$A$2); that told me that "Phone:" started in position 82, and I added 7 to that to tell me that the actual phone number started in position 89.

That's great for the first line, the phone number and the email address. But how do you find where the street address and city/state/ZIP lines start? There are no special character strings to mark them.

Well, there are, though. All along, I've been assuming that you have multiple lines of text in each cell, that is, that the above address is in one cell, not spread out among seven, one line to each. If so, then the lines are almost certainly separated by a line feed. Now, a line feed is a special non-printable control character, so it'd be difficult to get your keyboard to put it in between two quotes. In VBA you can refer to it as VbLf; but I just tried that in a FIND command ("=FIND(VbLf,A1)") and Excel doesn't understand it. But the numeric code for a line feed is a decimal 10, and Excel understands "CHAR(10)" just fine:

Code:

=FIND(CHAR(10),$A$2)

That returns a 15, meaning that the linefeed character is the 15th character in the cell—which in turn means that the second line starts in position 16. So here's how I picked out the second line of the address in my own test:

Code:

A1: Calvary Chapel
    Ellensburg 1.9 miles
    840 Cowboy Lane
    Ellensburg, Washington 98926
    Phone: 509-925-1651
    Email:taddscheffer@gmail.com
    Visit Website Get Directions
B1: =FIND(CHAR(10),$A2)
C1: =FIND(CHAR(10),$A2,B2+1)
D1: =FIND(CHAR(10),$A2,C2+1)
E1: =MID($A2,C2+1,D2-C2)

The FIND in column B tells me that the first LF is in position 15. The FIND in column C looks for the second LF—the extra argument is a starting position, so it starts looking after the first one—and that says the second LF is in position 36. The third FIND says that the third LF is in position 52. And the MID function picks out the third line; C2+1 is the starting position (after the second LF), and D2-C2 is the length.

Now, you'll still have to do a lot of experimenting to make everything work just right. But I think this is enough information to get you started. If not, just ask.