I downloaded a POI file of BOA locations for my Garmin. The problem is that it has almost all of the locations across the US. 9515 locations to be exact.
I have managed to get it to WA, ID, OR, MT (somewhat) but I can't get it sorted so just those states show up.It is interspersed with MI, AL, AK, just to name a few. What am I doing wrong here and how can I do this? What I want to be able to do is sort this by City, State. so I can upload them to my Garmin as separate POI Files
I am attaching an amended spreadsheet in .xls format.
Sorting
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sorting
See the attached version. I used some intermediate formulas to extract the city and state, so you can sort on them.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 23
- Joined: 26 Jan 2010, 00:03
Re: Sorting
Thank you so much Hans. I was able to sort out everything except WA,OR,ID, and MT, which is what I wanted. I have no clue as to what all of those formulas are about, but I read up on them. Still don't have a clue
Now all I should have to do is put each state location into its own .csv file and download them into my Garmin Waypoints. I will let you all know how that works out
You are an ACE !
Now all I should have to do is put each state location into its own .csv file and download them into my Garmin Waypoints. I will let you all know how that works out
You are an ACE !
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sorting
The formulas in column E return the position of the line break CHAR(10) in column D.
The formulas in column F return the position 7 characters before the last character in column D, i.e. the start of the state.
The formulas in column G return the part of the text in column D starting after the line break, and ending before the start of the state, i.e. the city.
The formulas in column H return 2 characters of the text in column D from the start of the state, i.e. the state abbreviation.
The formulas in column F return the position 7 characters before the last character in column D, i.e. the start of the state.
The formulas in column G return the part of the text in column D starting after the line break, and ending before the start of the state, i.e. the city.
The formulas in column H return 2 characters of the text in column D from the start of the state, i.e. the state abbreviation.
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 23
- Joined: 26 Jan 2010, 00:03
Re: Sorting
Ok, that makes some sense but me thinks I've got some reading up to do.
So let me ask you this: How would it look if I wanted to show the zip codes?
John
So let me ask you this: How would it look if I wanted to show the zip codes?
John
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Sorting
The zip code is easy: it consists of the last 5 characters in column D (assuming they are all 5 digit zip codes). So you can enter
=RIGHT(D1,5)
in a cell in row 1, say in I1, and fill down as far as needed.
=RIGHT(D1,5)
in a cell in row 1, say in I1, and fill down as far as needed.
Best wishes,
Hans
Hans