Address format in excel file

[Bryan Lane]

I want to distribute customer lists to our salesman that I will export to excel and email to each salesman. The phone number works great as a "click to use" field that launches the Phone Application.

I want the address to be a field they can click and have it launch the Maps app and display the address on the map.

I tried putting the address in two cells, with street address in one and City, ST and Zip below and also tried street, city, ST Zip all in one cell. Neither results in a clickable field.

I know they can highlight, copy, open Maps, paste, Search; but that is not an acceptable solution.

What does it take to create such a link?

Thanks,
Bryan

 

[noaim]

The reason that the phone number is clickable is because the iPhone recognizes phone numbers system wide theres not the same function with an address.. only in contact cards I believe..

 

[kch50428]

Here's what you can do with the client list...

Make a contact list that can be accessed by the salesman... and from the contact list, the address can be tapped, the native Maps app opens up... or, any number of GPS/PND apps can be made to "go to" an address from your contacts.

 

[bonesb]

It's late and been a long day, but IMO you're using the incorrect "tool" - Excel is a PITA and doesn't incorporate the features you're looking for. Use Word and it's a piece of cake.

First, get your data out of Excel by parsing it with uniform delimiters (the "Space" character works on the iPhone). An end resulting data string would look like (without the quotes) "900 Marine Drive Astoria OR 97103". If you don't know how to parse data with the Text To Columns command, you need help here - stick to the "Space" key, trust me here.

Next, get your data out of Excel and into Word. Highlight one or more rows and Copy. Switch to Word and use the Paste Special... command, and use the Unformatted Text option. Now you have one or more lines that look like the "900 Marine Drive Astoria OR 97103" string on each line. Now, the trick...

FOR EACH LINE, you'll want to add one of two strings, which you can copy from this post:
If you want to show a location in the Maps app add EXACTLY this string before each address:
Google Maps
(End result - 900 - Google Maps Marine Drive Astoria OR 97103)

If you want to be able to allow your end user to be able to navigate from their current location to an address in the Maps app add EXACTLY this string before each address:
Current Electrical Systems Murray - Google Maps
(End result - Current Electrical Systems Murray to 900 E @ 7075 S - Google Maps Marine Drive Astoria OR 97103)

I have found that this works well without the ZIP/Postal Codes as well and across country borders. No, we're not done yet, and it's why I've used Word here - it's a very powerful tool for formatting text and scripting commands.

Don't fret about having to copy the mapping strings - it's easy. Go back to the very first line and place the cursor at the front of the line and add a hard return. Now, use Search & Replace to replace all of the Paragraph returns with the string of your choice PLUS a replacement Paragraph return. You'd replace the paragraph character - ^P - with the following string (without the quotes) "http://maps.google.com/maps?f=q&q=^P"

Then, you'll need to select each line individually and change it to a Hyperlink. Word seems to get that a line with the URL-required characters are going to be a web link and places the highlighted text in the two Hyperlink fields in the Command Dialog Box.

No, it's not perfect and automated, but it works. I tend to read every single line that goes out of my office and not trust applications to fully automate something for me, especially if your employees are taking hours to drive somewhere - it's worth 10 seconds to hand-check each line IMO.

It may seem a bit daunting, but the whole conversion and appending process would take me maybe 2 minutes, and maybe 5 minutes to figure out how to write a VB script to automate the hyperlinking of each line. So, there you go. BTW, this is something I do. Don't use Excel.

 

[Bryan Lane]

Thanks to all three of you for your replies. I knew why the phone link works and was hoping there was a way to make it recognize address as well.

Bonesb: I'll give Word a try. I am exporting the data through Crystal Reports to Excel. I will test it first in Word to get it right and then see if I can incorporate your code suggestions into the Crystal Report prior to export to Word.

BTW: I love your "next device," I ride a Trek 2.3; only partially carbon, but not as much of an investment as to top of the line Colnago. I'm a 50+, 250 mile per week rider.

 

[bonesb]

Hope I helped out with that, but maybe you can cut Excel out of the picture now that you've IDed CR as your database, but it depends on which version of CR you're on and options you have. I'm not a user of CR - I'm a Mac user now but still run XP via Parallels so I can use a few legacy Windows apps, but not a Windows snob at all FWIW. I learned my trick through trial and error when I was processing chunks of data for light rail alignment design runtimes - because my bosses at the time wanted both reporting (Excel Report Manager) and pretty (Word), and of course all of the data was in Access, Primavera, and an Autodesk app. Your ticket to data portability is the CSV data format, but you'd need to be able to export from CR to CSV first, and some versions don't do that (but check out this workaround for CR - How to export report in CSV format : The Official Microsoft ASP.NET Forums - it might help?).

If you can get CSV out of CR, save the file. Open Word - here's the "trick" - use the "Open" command and navigate to your generated CSV file BUT also select the "Recover Text From Any File" option in the file type pulldown menu (which essentially strips out any embedded formatting) and you should find your data fields separated by commas and records separated by hard returns. Then it's simply a matter of using search and replace to swap out the commas (or other characters CR puts in for the desired "space" character I alluded to in my earlier post). QED. And, scriptable via a VB macro too. It doesn't cut out a "step" but rather swaps out one tool for another that's the right, uh, hammer to bang your data into the hole you want to fill (yes, I'm being silly here.

Now about our affliction. I opted for a Colnago, one of the Italian-made lugged bikes because I'm about 6"7" - I have a 2006 C50 in PR00 much like Serhiy Popoff's (Colnago Gallery - Colnago bike enthusiasts) but I opted for Campy Eurus wheels, I'm running the same Record 10 components as Popoff (which look like the current alu Athena package. Colnago is one of the very few bike manufacturers that have the sizes I want off the rack, with a fitting its as good as a custom for me - my C50 is a traditional 65cm frame. It's not my biggest bike, nor am I a carbon snob - I also have and ride a 1977 Raleigh Super Grand Prix, an English-made 26-inch frame with most of the original components that weighs a bunch but it's a smooth ride! I spent too much on attorneys this year or I would have bought my C59 this year - sigh.

If you'd consider it, one can save a bunch of coin by buying a few makes like Colnago from a UK seller like Slane Cycles in Belfast or Maestro UK (Mike), completely built up with Campy or Shimano and shipped to you for a huge price break (we don't pay VAT in the US, and they break down US pricing and even include the US Customs documents for UPS). Over there, for perspective, some Colnagos about the same price as that Trek you're riding here in NA. Slane and Mike have been catering to US buyers for over a decade, although Mike's gotta be close to retiring sometime soon as he's been in cycling for at least 35 years...

Cheers!

 

[Bryan Lane]

Got involved in other projects today, so I haven't gotten back to this yet. I hope to be able to dabble in it tomorrow.

Only 6'7" huh? I got you by an inch. LOL. The Trek has served me well for the last year. Since you referenced a Northwest address in your original post I assume you are from my neck of the woods (I'm in Tacoma). I am thinking about tackling STP this year....

 

[kch50428]

With all due respect to bonesb... that solution looks to be a lot of time-consuming work that I believe can be done quicker, and easier by taking the CSV file you export from Crystal Reports, and import that CSV into an address book/contact list that can be synced to the iPhone as a group in the phone's address book... export the CSV, import, sync, done.

 

[bonesb]

With all due respect to bonesb... that solution looks to be a lot of time-consuming work that I believe can be done quicker, and easier by taking the CSV file you export from Crystal Reports, and import that CSV into an address book/contact list that can be synced to the iPhone as a group in the phone's address book... export the CSV, import, sync, done.

I respectfully disagree. Try it with about 100 records. Using Windows. I could do it in maybe 5 minutes the first time and 2 minutes thereafter, and I'm a civil engineer - not a database designer that could do it quicker or an IT guy that could script both applications to do it in seconds. Email one file and done or email or update a contact list? I'll choose the former. And, I believe strongly in the vCard format too. A user of Crystal Reports can bang it out in nothing flat - they've got a really deep support network that's years deep and I've been scripting MS Office apps for 20 years - what I posed is easy stuff and reproducible.

 

[Bryan Lane]

I don't want them to appear in the Contacts. Most of the records will be "delivery address" records for customers with multiple job sites. We don't want them loaded as contacts that would need to be deleted when the job is completed (because that will never get done). That is why I choose the "export a file" via Excel (and now Word) method. 99 times out of 100 the salesman will not need any of the information in these records. This is for the one time when they need to know where the delivery needs to be made with no time to waste.

I'm sticking with the list. Unfortunately, all the other hats I wear during the day are demanding the attention I was hoping to direct at this project.

 

[bonesb]

Only 6'7" huh? I got you by an inch. LOL. The Trek has served me well for the last year. Since you referenced a Northwest address in your original post I assume you are from my neck of the woods (I'm in Tacoma). I am thinking about tackling STP this year....

Yeah, I'm *only* 6'7". I'm in Portland, but travel to Potlatch, Seattle, and Everett too for work and to hang out with friends. Tacoma's a pretty cool city, the benefits of being in that part of our world without the sh*t traffic around Seattle - this traffic comment coming from a NYer. A couple of Treks are on my short list (courtesy of Project 1) but I'm a Campy diehard and the bottom bracket cost for Treks to accommodate Campy cranks is a bit proud for me, but the Madone with an H2 fit in a 64cm looks sweet to me. STP? Not me, I'm generally a solo rider but totally nonjudgmental about it - I'm usually slammed with client work that time of year, but more power to you. That, and riding with a few thousand guys in cycling tights isn't my thing. Heh.

If I can get one of my lawsuits settled I'm considering a ride around parts of Australia for a few weeks. On my new Colnago?

Let me know if you're in the STP, I'll cheer you on as you're getting into town and get you properly hydrated when you're done!!!