Address format in excel file
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?
Last edited by BryanLane; 12-07-2011 at 02:25 PM.
- 12-07-2011, 06:57 PM #2
- 12-07-2011, 07:05 PM #3
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.
- 12-08-2011, 03:47 AM #4
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:
(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.
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.
- 12-08-2011, 01:44 PM #6
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...
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....
- 12-08-2011, 07:10 PM #8
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.
- 12-08-2011, 09:42 PM #9
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.
- 12-10-2011, 01:27 AM #11
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!!!