on 26/09/03 11:47, Chris Burton at [EMAIL PROTECTED] wrote: > Hi everyone > > I have a dilemma with hundreds of records of gps data. It has been > entered in 3 columns as > Degrees, Minutes and Seconds: eg 33 32 118 Unfortunately I > think it should have been in two columns as: > Degrees and decimal minutes: eg 33 32.118 > > Can someone please assist with a short cut to tell excel to add the > decimal point and the seconds column to the minutes, so I dont have to > go through and retype each record? Blowed if I can think of the > solution here, and the help is not that intuitive. I have thought of > the Concatenate function but it deals with text...is there another that > will use numbers and dots! > Hi Chris,
Forgive me for stating the obvious, but if the data really is in degrees/minutes/seconds and you want it in degrees/decimal minutes then you will need to actually convert it - ie divide seconds by 60 to get decimal minutes and then add to the whole minutes. However, from the example you give I'm assuming this is not the case (since 118>60 and the max you'd expect in the seconds column would be 59). So assuming the data in the second two columns IS minutes/decimal minutes your options depend on how the data is entered in the third column with respect to leading/trailing zeros, eg how would say .200, .020 and .002 minutes have been entered in the third column: - if just the digits after the decimal point have been entered, you would have a problem since "2" "02' and "002" will all equate to 2 and I suspect you would have to go through and retype the records to get the correct values. - assuming all three digits (including leading/trailing zeros) have been typed in ie "200" "020" "002" the number will be correctly entered as thousandths ie "200" "20" "2" and you can just do a conversion. 1) type "=RC[-2]+RC[-1]/1000" in column 4 (fill down to cover all records) 2) select and copy column 4 3) "paste special" into column 5 and paste "values" 4) delete columns 2,3 & 4 (a quick alternative would be to just do step 1 and then hide columns 2 & 3) Hope that helps. Cheers Neil -- Neil R. Houghton Albany, Western Australia Tel: +61 8 9841 6063 Fax: +61 8 9841 6137 Email: [EMAIL PROTECTED]
