Microsoft excel help

plonk

Hi

I hope someone can help.
I have 17 excel gps files that need to be edited in excel to just take out the lat long and elevation. I have uploaded a sample file. The normal ones contain about 800 lines and would take hours of editing.

I am wondering if there is a formula to take out certain boxes in sequence.
In the file attached the info I need are in the boxes c4, e4, j4 then again in c8, e8, j8, and finally c12, e12, and j12 so on and so on.

Thanks for your help

Also I wasnt sure if this is the right forum so mods feel free to move.

Thanks for your time and help

Razzuh

Hi,

I'm quite sure there's no way to delete rows with an excel formula. I know that you can do it with Virtual Basic for Applications (VBA) if you want to look into that (you'd need to be very computer literate though).

I noticed that all the cells you wanted had decimal numbers. You might be able to write a formula in excel based on that to select the appropriate cells.

You might know this, but if you end up donig it manually, the quickest way would be to select the columns you need first and copy them into a new spreadsheet. Then select the rows you need by holding down the 'ctrl' key and clicking on the appropriate row numbers. Once you have them all selected, click 'copy' and paste them in to a new worksheet. I reckon it shouldn't take you more than two hours for the workload you described using that method. That's the extent I can contribute I'm afraid. Good luck.

plonk

Thanks for the reply, I have already done one the way you suggested but I was hoping there would be and easier way, taking info from or selecting every 4th cell in column.

Its just annoying really, here is a full file. 917 times holding ctrl and clicking select and thats just for one column.

CreepingDeath

Use a filter to only show the $GPGGA lines.

In your example spreadsheet

1. Select your data, in this case A2 - O12
2. In the Excel "Home" tab, click on "Sort & Filter" in the editing box.
Click on "Filter".

Now you should have a drop down box in each column in row A.
In the A2 dropdown box, just select "$GPGGA".

Now you only see the rows you want, ie. A4, A8, A12 etc..

It's just a simple matter of selecting the columns now.

plonk

Havent tried it yet but it sounds like it will work.


Worked perfect thanks

rockal

Another way is to open a new sheet :-

In cell A1 type =INDEX(LINE11!$A:$J,ROW()*4,3)
In cell B1 type =INDEX(LINE11!$A:$J,ROW()*4,5)
In cell C1 type =INDEX(LINE11!$A:$J,ROW()*4,10)

Copy down as required.

Snowbat

Another method is to export the files to CSV and use AWK:

[snowbat@stoffice ~]$ awk '0 == NR % 4' line11.csv | awk -F, '{ print $3, $5, $10 }'
5307.447758 844.873717 18.41
5307.447654 844.873799 18.32
5307.447471 844.873999 18.27
5307.447471 844.873999 18.27
5307.447142 844.874464 18.03
5307.446727 844.874874 17.91
5307.446727 844.874874 17.91
5307.446403 844.875144 18.11
5307.446024 844.875543 18.04
5307.446024 844.875543 18.04

plonk

Ok so I need a little more help. I have attached a file. It contains gps points reading 5307.312629 and I need to format them so they read 53.07.31.2629 and 845.174298 reading 8.45.17.4298

Any help would be much appreciated.

Thanks

Snowbat

Are you trying to modify decimal degrees to feed something that requires degrees,minutes,seconds?
http://geography.about.com/library/howto/htdegrees.htm

mp22

is this any good.format cell-number-custom-00°00.000 ?

plonk

MP22 I actually need it like 53.07.31.2629 being degrees.minutes.seconds.milliseconds the milliseconds are the important part.

Edit: from your formula I managed to do it thanks

plonk

So now it won't let me find the degree symbol and replace with a full stop.

Any ideas?

Cheers

Edit: Got it just put the full stop in quotations

Thanks for all your help

plonk

Snowbat wrote: »

Are you trying to modify decimal degrees to feed something that requires degrees,minutes,seconds?
http://geography.about.com/library/howto/htdegrees.htm

Is there anyway to batch do this in excel.

Cheers

Snowbat

Yes, though the method I found uses separate cells for degrees, minutes and seconds: http://www.mail-archive.com/excel-macros@googlegroups.com/msg05706.html

I cooked up the following in OpenOffice Calc and checked a few rows using http://www.fcc.gov/mb/audio/bickel/DDDMMSS-decimal.html. Some rows removed due to xls attachment size limit.

plonk

Snowbat wrote: »

Yes, though the method I found uses separate cells for degrees, minutes and seconds: http://www.mail-archive.com/excel-macros@googlegroups.com/msg05706.html

I cooked up the following in OpenOffice Calc and checked a few rows using http://www.fcc.gov/mb/audio/bickel/DDDMMSS-decimal.html. Some rows removed due to xls attachment size limit.

Cheers snowbat

I'll use this figure 5307.250138
However the 53 is degrees 07 is the minutes but the problem lies with the seconds being a decimal so its the .250138 should turn in to 15.00828 seconds

thanks for your time

Snowbat

Like this?

(edit: Bug: Values over .50 cause a roundup)
(edit2: Bug fixed, attachment updated)