SPLIT string

[sal21]

ihave this string:

1-[40.986869,15.475182]

how get the separate part of string splitted from "," and have:

40.986869
15.475182

note:
the lenght of two number string are dinamic.

[HansV]

Code: Select all

    Dim MyString As String
    Dim Part1 As String
    Dim Part2 As String
    Dim p1 As Long
    Dim p2 As Long
    Dim p3 As Long
    
    MyString = "1-[40.986869,15.475182]"
    p1 = InStr(MyString, "[")
    p2 = InStr(p1 + 1, MyString, ",")
    p3 = InStr(p2 + 1, MyString, "]")
    Part1 = Mid(MyString, p1 + 1, p2 - p1 - 1)
    Part2 = Mid(MyString, p2 + 1, p3 - p2 - 1)

[Doc.AElstein]

Another idea…

If only the lenft of the two number strings are dimanic, then there may be some variation of using Replace a few times that might be useful

Pseudo like
StrText = Replace(StrText, "1-[", "") ' take out "1-["
StrText = Replace(StrText, "]", "") ' take out "]"
StrText = Replace(StrText, ",", vbCr & vbLf) ' change the , for a line break

Or the same again , all together in a line
StrText = Replace(Replace(Replace(StrText, "1-[", ""), "]", ""), ",", vbCr & vbLf)


As example if you had this in A1 and A2 …_
1-[40.986869,15.475182]
1-[41.986869,15.47]
_.. then the macro below would give you like the following in C1:C4
40.986869
15.475182
41.986869
15.47

Code: Select all

 Option Explicit
Sub Dimanyexamples() ' http://www.eileenslounge.com/viewtopic.php?f=30&t=35955
Dim StrText As String
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")   '     http://web.archive.org/web/20200124185244/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/

' Put the range in clipboards
ActiveSheet.Range("A1:A2").Copy

' get range text out of (some bit of window probably) clipboard
objDataObject.GetFromClipboard
 Let StrText = objDataObject.GetText(): Debug.Print StrText

' use replace a few times
' Let StrText = Replace(StrText, "1-[", "", 1, -1, vbBinaryCompare)         '  take out "1-["
' Let StrText = Replace(StrText, "]", "", 1, -1, vbBinaryCompare)           '  take out "]"
' Let StrText = Replace(StrText, ",", vbCr & vbLf, 1, -1, vbBinaryCompare)  '  change the  ,  for  a line break
' ' or
Let StrText = Replace(Replace(Replace(StrText, "1-[", ""), "]", ""), ",", vbCr & vbLf)
 Debug.Print StrText

 Application.OnTime EarliestTime:=Now(), Procedure:="'PutBaCkiNcLiPboARds                """ & StrText & """        '"
End Sub
Sub Putbackinclipboards(ByVal StrText As String)
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")   '     http://web.archive.org/web/20200124185244/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/

' put back in clipboards
objDataObject.SetText Text:=StrText
objDataObject.PutInClipboard
'
 ActiveSheet.Range("C1:C4").Clear
' put modified string back in worksheet
 ActiveSheet.Paste Destination:=ActiveSheet.Range("C1")
End Sub

' Ref
' http://www.eileenslounge.com/viewtopic.php?f=30&t=31395#p242941
' http://www.eileenslounge.com/viewtopic.php?f=30&t=31489#p243731
' http://www.eileenslounge.com/viewtopic.php?f=30&t=31938#p247681
' http://www.eileenslounge.com/viewtopic.php?f=30&t=31849&start=20#p246887
' http://web.archive.org/web/20200124185244/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
' https://stackoverflow.com/questions/25091571/strange-behavior-from-vba-dataobject-gettext-returns-what-is-currently-on-the-c/54960767#54960767
' https://stackoverflow.com/questions/31439866/multiple-variable-arguments-to-application-ontime/59812342#59812342

Alan
_.________


Clipboard.xls https://app.box.com/s/g4b434h60uvxxpk1gfgy325nu4ro6o0u

[CData]

in can also be done in a query with calculated fields, rather than a module ... assuming format is fixed 1-[x,y]
such that you know X always starts as the 4th character and Y always starts right after the ","
use InStr to identify the "," symbol's location in the string count
then you can mid / left / right to parse the string and trim off the fixed characters 1-[ ] ...

not sure if this approach is helpful but it does offer a simple way to inspect your string manipulation....