Data format coversion in Excel (long)

[catalfamo1220]

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

 

[jindon]

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 Rosenfeld]

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

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

 

[catalfamo1220]

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]

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

 

[Ron Rosenfeld]

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

 

[catalfamo1220]

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

 

[Ron Rosenfeld]

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

 

[catalfamo1220]

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

 

[Ron Rosenfeld]

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

 

[Norman Jones]

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!

 

[jindon]

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.

 

[Ron Rosenfeld]

Hi Ron,


An interesting variation on a paranoid theme - I speak as an incurable
fellow sufferer!

Ah, but it is not paranoia when they really are after you!!


--ron

 

[Norman Jones]

Hi Ron,

Ah, but it is not paranoia when they really are after you!!

Indeed!

ROTFL