Rearrange the Latitude and Longitude data in a prescribed format

[paksexcel]

Hi all,

I am naive to VBA codes and stuck at rearrangement of Latitude and Longitude dataset.
I have Lat Long data in single column as displayed below
Row 1: (24.111553448297805 71.80388800799847 0.0 0.0;24.112437854299895 71.80957295000553 0.0 0.0;24.109048006151767 71.80965978652239 0.0 0.0;24.107890901931206 71.803960762918 0.0 0.0;24.111553448297805 71.80388800799847 0.0 0.0)

Row 2: (24.0883183 71.8525179 0.0 0.0;24.087981318621004 71.85273505747318 0.0 0.0;24.08760330899392 71.85192804783583 0.0 0.0;24.087990501027214 71.85173526406288 0.0 0.0;24.0883183 71.8525179 0.0 0.0)

Row 3: (24.179023402434364 72.56080001592636 0.0 0.0;24.178842637629955 72.56082180887459 0.0 0.0;24.1788546 72.5612706 0.0 0.0;24.179038695553288 72.56117485463619 0.0 0.0;24.179023402434364 72.56080001592636 0.0 0.0)

Row N:
As depicted in the above dataset, there are Latitude, Longitude, Height, Depth and ;(colon). and all the rows having different length of lat long values or observations.

I want above data as below

MULTYPOLYGON(((71.8038880079984 24.1115534482978,71.8095729500055 24.1124378542998,71.8096597865223 24.1090480061517,71.803960762918 24.1078909019312,71.8038880079984 24.1115534482978)))

I need not required Height and Depth. and colon has to be replaced with comma.

Thanks in advance.

 

[vletm]

paksexcel​

You should attach here a sample file with some data and expected results.

 

[paksexcel]

Please find attached files...

 

[vletm]

paksexcel​

Do You have a vision - how to get those Your expected result from Your raw data?
eg how to solve 71.8038880079984?

 

[paksexcel]

Dear vletm,

Many thanks for quick responses.
May ask in details for your previous query please. I am not getting that what exactly you want?

 

[vletm]

paksexcel​

71.8038880079984 is the 1st value in Your expected results.
There is none same value in Your Raw data.
So, someway someone has solved ... calculated it from Your Raw data.
So far, You've given many (text) values and expected results based those.
Is there any clear logic for that?

 

[BobBridges]

This isn't an Excel question; it seems to me he's just asking how to use VB syntax to convert one character string to another:
1) Split each line by semicolon (which for each line gets you five sets of coordinates of four numbers each)
2) Strip off the last two numbers from each group
3) Put the groups back together with a comma delimiter instead of the semicolon
4) Add "MULTIPLYPOLYGON(((" and ")))" at the front and back

I'm ignoring the issue of whether the numbers in his sample match the numbers in his output, and maybe I shouldn't.

 

[rlv01]

Perhaps something like this.

'''
Public Sub DataConvert()
    Dim Infile As String, Outfile As String
    Dim TextLine As String, Line As String
    Dim Latitude As String, Longitude As String
    Dim CharSet As String
    Dim Coordinates As Variant, RowGroup As Variant
    Dim I As Long

    CharSet = " "

    Infile = "C:\Temp\Test\Raw data.txt"              'edit to customize
    Outfile = "C:\Temp\Test\Output.txt"               'edit to customize


    If Not (Dir$(Infile) <> "") Then
        MsgBox Infile & " not found.", vbCritical, "File not found"    'halt macro processing
    End If

    Open Infile For Input Access Read As #1           ' Open text file for read only.
    Open Outfile For Output Access Write As #2        ' Open text file for write

    Do While Not EOF(1)                               ' Loop until end of file.
        Line Input #1, TextLine                       ' Read line into variable.

        TextLine = Application.Trim(TextLine)
        If Left(TextLine, 3) = "Row" And IsNumeric(Mid(TextLine, 5, 1)) Then
            Line = Split(Replace(TextLine, ")", "("), "(")(1)
            RowGroup = Split(Line, ";")
            Line = "MULTYPOLYGON((("
            For I = 0 To UBound(RowGroup)
                Coordinates = Split(RowGroup(I), " ")
                Latitude = Round(Val(Coordinates(0)), 13)
                Longitude = Round(Val(Coordinates(1)), 13)
                Line = Line & Longitude & " " & Latitude & " "
            Next I
            Line = Trim(Line) & ")))"

            'Debug.Print Line                          ' Print to Debug window.
            Print #2, Line                            ' Write to output file
        End If
    Loop

    Close #1                                          ' Close file.
    Close #2                                          ' Close file.
    
    MsgBox "Formatted data saved to file:" & vbCr & vbCr & Outfile
End Sub