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.
SPLIT string
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: SPLIT string
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)
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: SPLIT string
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
Alan
_.________
Clipboard.xls https://app.box.com/s/g4b434h60uvxxpk1gfgy325nu4ro6o0u
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
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- 3StarLounger
- Posts: 308
- Joined: 24 Dec 2015, 16:41
Re: SPLIT string
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....
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....