Data format coversion in Excel (long)

**catalfamo1220** · 12-07-2005, 07:30 PM

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

**jindon** · 12-07-2005, 10:46 PM

assuming the data starts from A1
result will be col B

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

Originally Posted by catalfamo1220

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

**catalfamo1220** · 12-08-2005, 04:53 PM

Originally Posted by jindon

assuming the data starts from A1
result will be col B

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

**jindon** · 12-08-2005, 09:55 PM

Originally Posted by catalfamo1220

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 · 12-08-2005, 01:30 AM

On Wed, 7 Dec 2005 17:30:38 -0600, catalfamo1220
<[email protected]> 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

**catalfamo1220** · 12-08-2005, 04:28 PM

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.

Ron Rosenfeld · 12-08-2005, 06:40 PM

On Thu, 8 Dec 2005 14:28:13 -0600, catalfamo1220
<[email protected]> 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

**catalfamo1220** · 12-08-2005, 07:07 PM

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 · 12-08-2005, 07:35 PM

On Thu, 8 Dec 2005 17:07:39 -0600, catalfamo1220
<[email protected]> 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