So, I copied a long list of addresses, phone numbers, etc into an excel spreadsheet. I stuck it on tab 3 because I figured I could somehow link the info I wanted to keep on tab 1, and in the format I desired. So far no luck.

Here is an example of how it is formatted currently-

Calvary Chapel Ellensburg 1.9 miles

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



Now, if I could have my perfect world, I would be able to have the needed information copy into a mailing label template. The above same is repeated with different addresses in one vertical column a few hundred times. The pattern is consistent, i.e. the amount of spaces from one name to the next is always the same. Copying it into a mailing label template would be great, even better (though I doubt it is possible) would be if the distance ("1.9 miles") could be deleted from the name and if the words "email:" could be deleted from each email. I don't know if excel can do that, but I know if does a lot more than I know about. I mainly use it for financial tables and formulas, so this formatting of info is not familiar to me.

Hope there is an answer, thanks for the help.

This is JUST the sort of thing a machine should do rather than a human! That is, you'll have to teach your machine to do it, but there's no way you should have to do it all manually, a few hundred times.

Getting the extra words out is certainly possible. You may have to play around a little to get it right, but I'm sure it'll be worth the effort. So what are you looking for in the end? Something like this?

That way you can have your mailing label (and I'm sure there's a way to teach Excel to reärrange those to fit any form you have), and still have the phone and email address on the same lines.

So do you want to do this with formulae, or with a VBA program? I might go for VBA just because I like it, but I imagine it's possible with formulae too.

The only really knotty problem that I see here might be how to separate the street from the city, which in your example are on the same line. Or is that an artifact of the way you pasted it in here? Are they in separate lines in your worksheet?

Awesome news so far!

1) I do not care how it gets done as long as it gets done.

2)yes, it looks like the formatting is different then when I pasted. Though I created it on my laptop and now I am on my phone. There are actually spaces in between as well.

Maybe I can attach it when I get back to my hotel.

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: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: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: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:
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.

So, I'm not 100% following you, but I think I'm getting close. I am going to attach a small sample of how my copied list looks once pasted in excel. Just to make sure I've conveyed my situation correctly.

Until I get another reply, I am going to try to play around with your suggestions.

Attached Files

Sample Address List.xls (19.0 KB, 11 views)

So, for what it is worth, I have it working so I can get only the text I want to show, which is awesome! Now I just need to find a way to arrange the info properly. Right now I have everything in column A and I'd like to format it on at least one tab/sheet so that I can print off labels. So it'd be just names and addresses, on probably 3-4 columns.

I just looked at the sample you posted 15:16 (east-coast time) and saw that what you have in your worksheet isn't what I predicted. I was going to post some further notions of how to handle it, but then I noticed you posted again saying you have it working right. That's great!

(Are you sure? How are you getting rid of the "Ellensburg 9.1 miles" part of the first line? I have an idea how to do it, but if yours is already working then I wouldn't want to quash your creativity :-). Besides, it may be better than mine.)

Three to four columns? I've never done address labels in Excel; I would have assumed you'd want to arrange each address in a cell. But you apparently already know how it works, and I don't. What else do you need from me? Any ideas?

I'm just trying to find a way to automatically relocate things. Normaly I create a formula for my needs and click and drag it down to repeat everything, but I cant figure out how to only copy the info I need. Maybe when I'm back on a laptop again I can show an example of what I'd want and then we could figure out how to get from one to the other.

Ok. While we're waiting for that to happen, here's one question: Do you expect to need to rearrange the address information like this only once or twice, or many times? If you're going to do it once a week for the rest of your life (so to speak), then it should be in a VBA program. If it's just this once, you may as well do it manually with copy-and-paste. VBA takes more trouble, but it's infinitely more flexible and (for some people at least) much more fun as well.

Ok, on different laptop now.

I attached a new sample. The list of data is on sheet 3 and I sample copied three entries onto sheet 1. Is there a way to make it so I can quickly get things to copy over in this format? Like click and drag?

I'm really not extremely well versed in Excel. Again, I mainly use it for simple math equations and tables of information. So a lot of what we're discussing is new for me.


Any ideas?

I don't see the new sample; if you're like me, that just means you meant to attach it and forgot.

Also, see my previous question, that I posted while you were away: Is this a one-time thing or will you do it again and again? If the latter, you really need to write a VBA program for it. If it's just one time, then I would use the FIND and MID formula (you said you got those working) and then just cut-and-paste the results the way you want them.

But before I recommend something I should see the Before and After photos.

I thought I attached it. Here it goes again.

Attached Files

Address Sample.xlsx (92.9 KB, 8 views)

Third time: Do you want to do this many times, or just once? (Are you one of those people who never manage to see more than one question per email? I usually have to end up sending them one email per question or issue, once I have them spotted, in order to get all the questions answered.)

Sorry, again, mostly replying by phone while on a bus or something. It's hard to write long emails and catch everything.

I don't know how many times I'd have to do this, I could probably make one long list and then do it once. It is mainly a list coming off of one website, but it would have hundreds of entries.

Well, here's what I'm thinking: You've already got it mostly arranged the way you want it, right? You could, at this point, just copy the address column into another worksheet and arrange it for those labels of yours. (As I said, I haven't printed to labels in years so I don't know what's involved in that, but I doubt you need to do much more arranging.) So at this point, you could just get on with the printing and don't need anything more from me. I'm assuming, in saying this, that whatever you have to do won't involve you doing it once for each address—that you can do it en masse, just as you did to get the incoming data whipped into the shape it's in now. I just don't know the details.

On the other hand, if this is going to be repeated—if you're going to have to go get new addresses from somewhere, and arrange them yet again into the current format, perhaps once a week for the next few years—then it's well worth your time to turn the process you've already developed into a VBA program. I can help you with that. But only if you need to ... or if you want to learn VBA just because you think it might be useful.