How can i split the next text field on space in VBA:
AEX*: 342,00 -2,71 -0,79% O: 345,37 H: 345,52 L: 341,67 C: 344,71
18:05:01 AEX-25: 2? 23? 0=
Hi Peter,
Am Sat, 29 Dec 2012 14:33:51 +0100 schrieb PeCoNe:
this string is in A52. You can split it and write back in row 52:
Sub Test()
Dim myArr As Variant
Dim i As Integer
With Sheets("IEX")
myArr = Split(.Range("A52"), " ")
For i = 0 To UBound(myArr)
.Cells(52, i + 1) = myArr(i)
Next
End With
End Sub
Regards
Claus Busch
Did you miss my last reply on previous problem?
see yesterday 21:40
============================================
[a53].Resize(columnsize:=UBound(Split([A52])) + 1) = Split([A52])
============================================
=========================================
[A52].TextToColumns Destination:=[a53], DataType:=xlDelimited, textqualifier:=xlTextQualifierDoubleQuote, _
consecutivedelimiter:=True, Tab:=False, semicolon:=False, comma:=False, Space:=True, _
other:=False
=========================================
BTW, it's Rick Rothstein, who I see here infrequently now, who is a fan of
"one-liners". Something today reminded me of him. That's why I posted
I too am a fan of 1 liners, inspired by Rick and so I'm also a fan of
his and his work!
Hi Garry,
Am Sat, 29 Dec 2012 16:51:07 -0500 schrieb GS:
me too, but one liners are for experts. The OP get help but he don't
understand.
Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
While lurking in another forum an OP had a similar query as the OP in this
thread. A large bundle of data needing to be seperated as shown here. You will notice there are actually five "sentences" numbered 1. 2. 3. 4. & 5.
1. BOYD GAMING CORP (BYD) 3883 Howard Hughes Pkwy., 9th Fl., Las Vegas, Nevada, United States PUBLIC - PARENT 1014789 2. CAESARS ENTERTAINMENT CORP (CZR) 1 Caesars Palace Dr., Las Vegas, Nevada, United States PUBLIC 1022989 3. INTERNATIONAL GAME TECHNOLOGY INC (IGT) 6355 S. Buffalo Dr., Las Vegas, Nevada, United States PUBLIC - PARENT 1018044 4. Las Vegas Sands Corp. (LVS) 3355 Las Vegas Blvd. South, Las Vegas, Nevada, United States PUBLIC - PARENT 1041040 5. MGM RESORTS INTERNATIONAL (MGM) 3600 Las Vegas Blvd. South, Las Vegas, Nevada, United States PUBLIC - PARENT
I tried to modify your orignal code to do the seperations at the 1. 2. 3. etc. by looping and using j to represent the 1. 2. 3.'s in the data instead of a space.
The result was the entire data was copied five times to individual rows below cell B2. Any hints on how to seperate the data 'by the numbers'?
Hi Howard,
the last string isn't correct.
Try:
Sub Test_J()
Dim myArr(0 To 4) As Variant
Dim i As Integer
Dim j As Integer
Dim MyStart As Integer
Dim myEnd As Integer
Dim myLen As Integer
With Sheets("Sheet1")
For j = 0 To 4
MyStart = WorksheetFunction.Find(j + 1 & ".", .[B2]) + 3
If j < 4 Then
myEnd = WorksheetFunction.Find(j + 2 & ".", .[B2]) - 1
Else
myEnd = Len(.[B2])
End If
myLen = IIf(j < 4, myEnd - MyStart, Len(.[B2]) - MyStart + 1)
myArr(j) = Mid(.[B2], MyStart, myLen)
Next
j = 0
For i = 0 To UBound(myArr)
.Cells(j + 3, i + 2) = myArr(i)
Next
End With
End Sub
or try:
Sub Test_J2()
Dim myArr(0 To 4) As Variant
Dim i As Integer
Dim j As Integer
Dim MyStart As Integer
Dim myEnd As Integer
Dim myLen As Integer
With Sheets("Sheet1")
For j = 0 To 4
MyStart = InStr(.[B2], j + 1 & ".") + 3
If j < 4 Then
myEnd = InStr(.[B2], j + 2 & ".") - 1
Else
myEnd = Len(.[B2])
End If
myLen = IIf(j < 4, myEnd - MyStart, Len(.[B2]) - MyStart + 1)
myArr(j) = Mid(.[B2], MyStart, myLen)
Next
j = 0
For i = 0 To UBound(myArr)
.Cells(j + 3, i + 2) = myArr(i)
Next
End With
End Sub
Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
I took a few minutes to see if I could alter the code to list the new data into seperate rows instead of columns. No luck. What would I change to list the new data in five rows below cell B2?
Hi Howard,
Am Sun, 30 Dec 2012 03:07:56 -0800 (PST) schrieb (e-mail address removed):
change:
j = 0
For i = 0 To UBound(myArr)
.Cells(j + 3, i + 2) = myArr(i)
Next
to:
For i = 0 To UBound(myArr)
.Cells(i + 3, 2) = myArr(i)
Next
Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
Hi Garry,
Am Sat, 29 Dec 2012 16:51:07 -0500 schrieb GS:
me too, but one liners are for experts. The OP get help but he don't
understand.
Regards
Claus Busch
I like to use regular expressions for this kind of problem. It provides a more flexible method of recognizing test patterns.
In this case, the problem can be expressed as returning strings that
Start with a digit followed by a dot followed by a space and
Ending with either another digit-dot-space sequence, or with the end ofthe string.
So the regex looks like:
Split Numbered Sentences
\d\.\s.*?(?=\d\. |$)
and is described as:
Match a single digit 0..9 «\d»
Match the character “.” literally «\.»
Match a single character that is a “whitespace character” (spaces, tabs, and line breaks) «\s»
Match any single character that is not a line break character «.*?»
Between zero and unlimited times, as few times as possible, expanding as needed (lazy) «*?»
Assert that the regex below can be matched, starting at this position (positive lookahead) «(?=\d\. |$)»
Match either the regular expression below (attempting the next alternative only if this one fails) «\d\. »
Match a single digit 0..9 «\d»
Match the character “.” literally «\.»
Match the character “ ” literally « »
Or match regular expression number 2 below (the entire group fails if this one fails to match) «$»
Assert position at the end of the string (or before the line break at the end of the string, if any) «$»
Created with RegexBuddy
As implemented in VBA code, using just A1 for the source:
======================================
Option Explicit
Sub SplitSentences()
Dim rng As Range, c As Range
Dim i As Long
Dim re As Object, mc As Object
Set rng = Range("A1")
Set re = CreateObject("vbscript.regexp")
With re
.Pattern = "\d\.\s.*?(?=\d\. |$)"
.Global = True
.MultiLine = True
End With
If re.test(rng.Text) = True Then
Set mc = re.Execute(rng.Text)
For i = 0 To mc.Count - 1
rng.Offset(rowoffset:=i + 1) = mc(i)
Next i
End If
End Sub
==============================
Of course, one could add more columns and loop through them; and should probably also clear out the cells below sufficiently. If there were a lot of data to process, and this ran slowly because of the overhead of reading/writing from the worksheet, I would dump everything into an array, work on it in the array, and then dump it back to the worksheet; but the above givesyou a an idea about a more powerful tool with which to process text strings.
I can take credit for the statement of the basic algorithm (Strings ending
with digit-dot-space or end of the string).
As far as the dissection of the regex itself, note the final line "Created
with RegexBuddy". The program itself generated that explanation. I find it
incredibly useful in developing and testing regular expressions. See
http://www.regexbuddy.com/