Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data format coversion in Excel (long)
I have a set of files which I need to change the formatting of. Th following is a "snippet" from the file. LINE ! GP 43 20 55.8104 071 00 21.9000 ! GP 43 22 55.9808 071 00 21.9000 ! LINE ! GP 42 20 26.3693 069 39 27.1135 ! GP 42 20 24.4404 069 36 45.5376 ! LINE ! GP 41 20 53.1930 071 00 21.9000 ! GP 41 18 53.3019 071 00 21.9000 ! LINE ! GP 42 20 26.3693 072 21 16.6865 ! GP 42 20 24.4404 072 23 58.2624 ! LINE ! GP 42 04 54.7989 072 13 28.5131 ! GP 42 03 21.2430 072 20 22.2052 ! LINE ! GP 43 15 19.9995 071 21 43.4900 ! GP 43 20 29.4271 071 23 47.0573 ! LINE ! GP 42 36 07.9703 069 46 39.0272 ! GP 42 37 32.2043 069 39 38.1326 ! LINE ! GP 41 26 25.2752 070 39 36.5962 ! GP 41 21 15.2893 070 37 40.2406 ! LINE ! GP 43 22 25.0574 072 25 21.6600 ! GP 43 22 25.0574 069 35 22.1399 ! LINE ! GP 43 22 25.0574 069 35 22.1399 ! GP 41 18 23.3610 069 38 06.2447 ! LINE ! GP 41 18 23.3610 069 38 06.2447 ! GP 41 18 23.3610 072 22 37.5553 ! LINE ! GP 41 18 23.3610 072 22 37.5553 ! GP 43 22 25.0574 072 25 21.6600 ! LINE ! GP 42 20 26.3693 069 39 27.1135 ! GP 42 19 06.7204 069 39 29.9999 ! GP 42 17 47.1379 069 39 35.2571 ! GP 42 16 27.6609 069 39 42.8798 ! GP 42 15 08.3279 069 39 52.8620 ! GP 42 13 49.1778 069 40 05.1963 ! What I need Excel (or any other fesable ways of doing this) to do i get this coordinate data in a format like such: N043.20.55.810 W071.00.21.900 N043.22.55.981 W071.00.21.900 N042.20.26.369 W069.39.27.114 N042.20.24.440 W069.36.45.538 N041.20.53.193 W071.00.21.900 N041.18.53.302 W071.00.21.900 What happens is as follows: 1. 1st two *digits* get a "N0" to proceed them 2. A decimal is added after the first 2, second, and third digits 3. If there's 4 digits in the 4th set of numbers, it gets divided by 1 and rounded to nearest tens place 4. Same happens for the other 4 "sets" 5. Every other line of coordinates gets added directly after the on before it Anyone confused yet? Let me know if further explanation i necessary. Would Excel be a fesable solution to this? If not, can someone point m to the right direction? Thanks for your patience. Mike Catalfam -- catalfamo122 ----------------------------------------------------------------------- catalfamo1220's Profile: http://www.excelforum.com/member.php...fo&userid=2945 View this thread: http://www.excelforum.com/showthread.php?threadid=49167 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data format coversion in Excel (long)
assuming the data starts from A1 result will be col B Code: -------------------- Sub test() Dim a, i As Long, ii As Long Dim txt As String, x, result() With ActiveSheet a = .Range("a1").CurrentRegion.Value For i = 1 To UBound(a, 1) If InStr(a(i, 1), "GP") 0 Then a(i, 1) = _ Trim(Replace(Replace(Replace(a(i, 1), "GP", ""), "!", ""), ".", " ")) x = Split(a(i, 1)) If Val(x(3)) 999 Then x(3) = _ Application.Round(Val(x(3)) / 10, -2) If Val(x(3)) = 1000 Then x(3) = 990 If Val(x(7)) 999 Then x(7) = _ Application.Round(Val(x(7)) / 10, -2) If Val(x(7)) = 1000 Then x(7) = 990 txt = "NO" & x(0) & "." & x(1) & "." & x(2) & "." & Val(x(3)) & _ " W" & x(4) & "." & x(5) & "," & x(6) & "." & Val(x(7)) If InStr(a(i + 1, 1), "GP") 0 Then a(i + 1, 1) = _ Trim(Replace(Replace(Replace(a(i + 1, 1), "GP", ""), "!", ""), ".", " ")) x = Split(a(i, 1)) If Val(x(3)) 999 Then x(3) = _ Application.Round(Val(x(3)) / 10, -2) If Val(x(3)) = 1000 Then x(3) = 990 If Val(x(7)) 999 Then x(7) = _ Application.Round(Val(x(7)) / 10, -2) If Val(x(7)) = 1000 Then x(7) = 990 txt = txt & Chr(32) & _ "NO" & x(0) & "." & x(1) & "." & x(2) & "." & Val(x(3)) & _ " W" & x(4) & "." & x(5) & "," & x(6) & "." & Val(x(7)) ii = ii + 1: ReDim Preserve result(ii): result(ii) = txt: txt = Empty i = i + 1 End If End If Next Erase a .Range("b1").Resize(UBound(result)) = Application.Transpose(result) Erase result End With End Sub -------------------- catalfamo1220 Wrote: I have a set of files which I need to change the formatting of. The following is a "snippet" from the file. What I need Excel (or any other fesable ways of doing this) to do is get this coordinate data in a format like such: What happens is as follows: 1. 1st two *digits* get a "N0" to proceed them 2. A decimal is added after the first 2, second, and third digits 3. If there's 4 digits in the 4th set of numbers, it gets divided by 10 and rounded to nearest tens place 4. Same happens for the other 4 "sets" 5. Every other line of coordinates gets added directly after the one before it Anyone confused yet? Let me know if further explanation is necessary. Would Excel be a fesable solution to this? If not, can someone point me to the right direction? Thanks for your patience. Mike Catalfamo -- jindon ------------------------------------------------------------------------ jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135 View this thread: http://www.excelforum.com/showthread...hreadid=491671 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data format coversion in Excel (long)
On Wed, 7 Dec 2005 17:30:38 -0600, catalfamo1220
wrote: I have a set of files which I need to change the formatting of. The following is a "snippet" from the file. LINE ! GP 43 20 55.8104 071 00 21.9000 ! GP 43 22 55.9808 071 00 21.9000 ! LINE ! GP 42 20 26.3693 069 39 27.1135 ! GP 42 20 24.4404 069 36 45.5376 ! LINE ! GP 41 20 53.1930 071 00 21.9000 ! GP 41 18 53.3019 071 00 21.9000 ! LINE ! GP 42 20 26.3693 072 21 16.6865 ! GP 42 20 24.4404 072 23 58.2624 ! LINE ! GP 42 04 54.7989 072 13 28.5131 ! GP 42 03 21.2430 072 20 22.2052 ! LINE ! GP 43 15 19.9995 071 21 43.4900 ! GP 43 20 29.4271 071 23 47.0573 ! LINE ! GP 42 36 07.9703 069 46 39.0272 ! GP 42 37 32.2043 069 39 38.1326 ! LINE ! GP 41 26 25.2752 070 39 36.5962 ! GP 41 21 15.2893 070 37 40.2406 ! LINE ! GP 43 22 25.0574 072 25 21.6600 ! GP 43 22 25.0574 069 35 22.1399 ! LINE ! GP 43 22 25.0574 069 35 22.1399 ! GP 41 18 23.3610 069 38 06.2447 ! LINE ! GP 41 18 23.3610 069 38 06.2447 ! GP 41 18 23.3610 072 22 37.5553 ! LINE ! GP 41 18 23.3610 072 22 37.5553 ! GP 43 22 25.0574 072 25 21.6600 ! LINE ! GP 42 20 26.3693 069 39 27.1135 ! GP 42 19 06.7204 069 39 29.9999 ! GP 42 17 47.1379 069 39 35.2571 ! GP 42 16 27.6609 069 39 42.8798 ! GP 42 15 08.3279 069 39 52.8620 ! GP 42 13 49.1778 069 40 05.1963 ! What I need Excel (or any other fesable ways of doing this) to do is get this coordinate data in a format like such: N043.20.55.810 W071.00.21.900 N043.22.55.981 W071.00.21.900 N042.20.26.369 W069.39.27.114 N042.20.24.440 W069.36.45.538 N041.20.53.193 W071.00.21.900 N041.18.53.302 W071.00.21.900 What happens is as follows: 1. 1st two *digits* get a "N0" to proceed them 2. A decimal is added after the first 2, second, and third digits 3. If there's 4 digits in the 4th set of numbers, it gets divided by 10 and rounded to nearest tens place 4. Same happens for the other 4 "sets" 5. Every other line of coordinates gets added directly after the one before it Anyone confused yet? Let me know if further explanation is necessary. Would Excel be a fesable solution to this? If not, can someone point me to the right direction? Thanks for your patience. Mike Catalfamo Are the latitudes and longitudes in one cell or two cells initially? Do you want the output to be in individual cells or all four combined into one cell? --ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data format coversion in Excel (long)
Ron, The initial coordinate data is in a text file. When I import into excel I can divide it up into however many columns I need to. I have no preference as to the end result of the data, 1 or 2 cells. Appreciate the help. :) -- catalfamo1220 ------------------------------------------------------------------------ catalfamo1220's Profile: http://www.excelforum.com/member.php...o&userid=29458 View this thread: http://www.excelforum.com/showthread...hreadid=491671 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data format coversion in Excel (long)
jindon Wrote: assuming the data starts from A1 result will be col B Code: -------------------- Sub test() Dim a, i As Long, ii As Long Dim txt As String, x, result() With ActiveSheet a = .Range("a1").CurrentRegion.Value For i = 1 To UBound(a, 1) If InStr(a(i, 1), "GP") 0 Then a(i, 1) = _ Trim(Replace(Replace(Replace(a(i, 1), "GP", ""), "!", ""), ".", " ")) x = Split(a(i, 1)) If Val(x(3)) 999 Then x(3) = _ Application.Round(Val(x(3)) / 10, -2) If Val(x(3)) = 1000 Then x(3) = 990 If Val(x(7)) 999 Then x(7) = _ Application.Round(Val(x(7)) / 10, -2) If Val(x(7)) = 1000 Then x(7) = 990 txt = "NO" & x(0) & "." & x(1) & "." & x(2) & "." & Val(x(3)) & _ " W" & x(4) & "." & x(5) & "," & x(6) & "." & Val(x(7)) If InStr(a(i + 1, 1), "GP") 0 Then a(i + 1, 1) = _ Trim(Replace(Replace(Replace(a(i + 1, 1), "GP", ""), "!", ""), ".", " ")) x = Split(a(i, 1)) If Val(x(3)) 999 Then x(3) = _ Application.Round(Val(x(3)) / 10, -2) If Val(x(3)) = 1000 Then x(3) = 990 If Val(x(7)) 999 Then x(7) = _ Application.Round(Val(x(7)) / 10, -2) If Val(x(7)) = 1000 Then x(7) = 990 txt = txt & Chr(32) & _ "NO" & x(0) & "." & x(1) & "." & x(2) & "." & Val(x(3)) & _ " W" & x(4) & "." & x(5) & "," & x(6) & "." & Val(x(7)) ii = ii + 1: ReDim Preserve result(ii): result(ii) = txt: txt = Empty i = i + 1 End If End If Next Erase a .Range("b1").Resize(UBound(result)) = Application.Transpose(result) Erase result End With End Sub -------------------- Ron, I tried running this and I get the following: Runtime Error #9 Subscript out of Range Mike C -- catalfamo1220 ------------------------------------------------------------------------ catalfamo1220's Profile: http://www.excelforum.com/member.php...o&userid=29458 View this thread: http://www.excelforum.com/showthread...hreadid=491671 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data format coversion in Excel (long)
On Thu, 8 Dec 2005 14:28:13 -0600, catalfamo1220
wrote: Ron, The initial coordinate data is in a text file. When I import into excel I can divide it up into however many columns I need to. I have no preference as to the end result of the data, 1 or 2 cells. Appreciate the help. :) One way to do this is with "regular expressions". I will assume that your text file gets imported line by line as you have posted it, and the contents of each cell looks like: GP 43 20 55.8104 071 00 21.9000 ! Here is one method of getting the data into the format you have posted. 1. Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr 2. With your data in column A, the latitude can be parsed out and converted to your desired format with the formula: =REGEX.SUBSTITUTE(A2,".*GP\s(\d{2})\s(\d{2})\s(\d{ 2}\.\d{3}).*","N0[1]\.[2]\.[3]") 3. The longitude can be parsed out and converted to your desired format with the formula: =REGEX.SUBSTITUTE(A2,".*\s(\d{3})\s(\d{2})\s(\d{2} \.\d{3}).*","W[1]\.[2]\.[3]") We're getting ready to sit down to dinner, so I don't have the time right now to put the two lines together into one the way you really want, but the above should get you started. I look forward to your impression of this method. Best, --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data format coversion in Excel (long)
Thanks Ron. I'm pretty new at this so if you could help me out as to exactly where the formula should go I'd appreciate it. It seems that what I'm doing now isn't doing anything. Mike -- catalfamo1220 ------------------------------------------------------------------------ catalfamo1220's Profile: http://www.excelforum.com/member.php...o&userid=29458 View this thread: http://www.excelforum.com/showthread...hreadid=491671 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data format coversion in Excel (long)
On Thu, 8 Dec 2005 17:07:39 -0600, catalfamo1220
wrote: Thanks Ron. I'm pretty new at this so if you could help me out as to exactly where the formula should go I'd appreciate it. It seems that what I'm doing now isn't doing anything. Mike I don't know what you mean when you write "isn't doing anything". Do you get a blank or an error message? If the latter, what is it? Your imported data must be someplace on your worksheet. Where you see the cell reference (A1) in the formula, substitute the cell reference for the location of your data. --ron |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data format coversion in Excel (long)
I substituted in all of the "A" column (including the lines which say "LINE !") and I recieve the following when I enter the formula: "#NUM!" Mike -- catalfamo1220 ------------------------------------------------------------------------ catalfamo1220's Profile: http://www.excelforum.com/member.php...o&userid=29458 View this thread: http://www.excelforum.com/showthread...hreadid=491671 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data format coversion in Excel (long)
On Thu, 8 Dec 2005 17:50:18 -0600, catalfamo1220
wrote: I substituted in all of the "A" column (including the lines which say "LINE !") and I recieve the following when I enter the formula: "#NUM!" Mike Are you sure it's #NUM! and not #NAME? error? If it is #NAME?, then the problem is that you either have a typo in the function, or you did not install the morefunc.xll add-in. The #NUM! error is not documented as a return for REGEX.SUBSTITUTE (or any of the related functions). But that could be a limitation of the documentation, I suppose. I guess there could be differences in your strings from what you've posted here, that are not apparent. If you are, indeed, getting the #NUM! error, perhaps you could email me a copy of the worksheet. If you reverse the following string and then make the obvious substitution, it should reach me moc.enilnodlefnesorTAzyx --ron |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data format coversion in Excel (long)
Hi Ron,
If you reverse the following string and then make the obvious substitution, it should reach me moc.enilnodlefnesorTAzyx An interesting variation on a paranoid theme - I speak as an incurable fellow sufferer! --- Regards, Norman "Ron Rosenfeld" wrote in message ... On Thu, 8 Dec 2005 17:50:18 -0600, catalfamo1220 wrote: I substituted in all of the "A" column (including the lines which say "LINE !") and I recieve the following when I enter the formula: "#NUM!" Mike Are you sure it's #NUM! and not #NAME? error? If it is #NAME?, then the problem is that you either have a typo in the function, or you did not install the morefunc.xll add-in. The #NUM! error is not documented as a return for REGEX.SUBSTITUTE (or any of the related functions). But that could be a limitation of the documentation, I suppose. I guess there could be differences in your strings from what you've posted here, that are not apparent. If you are, indeed, getting the #NUM! error, perhaps you could email me a copy of the worksheet. If you reverse the following string and then make the obvious substitution, it should reach me moc.enilnodlefnesorTAzyx --ron |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data format coversion in Excel (long)
catalfamo1220 Wrote: Ron, I tried running this and I get the following: Runtime Error #9 Subscript out of Range Mike C According to the data you have provided and if they are all in ColA, it should work. Possibility to get that kind of error is to have the data which is not following the format with the data you have provided. -- jindon ------------------------------------------------------------------------ jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135 View this thread: http://www.excelforum.com/showthread...hreadid=491671 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data format coversion in Excel (long)
On Fri, 9 Dec 2005 01:49:29 -0000, "Norman Jones"
wrote: Hi Ron, If you reverse the following string and then make the obvious substitution, it should reach me moc.enilnodlefnesorTAzyx An interesting variation on a paranoid theme - I speak as an incurable fellow sufferer! --- Regards, Norman Ah, but it is not paranoia when they really are after you!! --ron |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data format coversion in Excel (long)
Hi Ron,
Ah, but it is not paranoia when they really are after you!! Indeed! ROTFL --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Interrupting long calculations in Excel? Hi all, I am having a verylong calculation in Excel using VBA. Of course I know I can hit ESC orCTRL+BREAK to interrupt the VBA program. But I found due to too long runningprocess, the Excel window stop to | Excel Discussion (Misc queries) | |||
Does Excel have a cell format for lat/long in ° min sec format? | Excel Discussion (Misc queries) | |||
Text Time Coversion Arithmatic | Excel Discussion (Misc queries) | |||
cell format in Excel 2002 SP3 will not accept long numbers | Excel Worksheet Functions | |||
Coversion Of Fractions To Percentages | Excel Discussion (Misc queries) |