EXTRACTING SUBSTRINGS !!

[jay dean]

Hello -

Dim A as string
Dim brkout() as string
Dim B as string, C as string, D as string, E as string, Leftover as
string

A contains text. I am trying to store the first 4 substrings of A in B,
C, D, and E. Then I want whatever else is left to be stored in Leftover.
The delimiter for the substrings is ".."

I have been able to use the SPLIT() function to store the first 4
strings using brkout=split(A,"..")
then B=brkout(1):C=brkout(2)=brkout(3):E =brkout(4)

How do I store the remaining text left in brkout() (apart from B,C,D and
E) in Leftover?

Any help would be appreciated.

Jay

 

[Per Jessen]

HI Jay

Try this:

Sub aaa()
Dim A As String
Dim brkout() As String
Dim B As String, C As String, D As String, E As String, Leftover As
String
Const Delemiter As String = ", "

brkout = Split(A, "..")
B = brkout(1)
C = brkout(2)
D = brkout(3)
E = brkout(4)

If UBound(brkout) > 4 Then
For x = 5 To UBound(brkout)
If Leftover = "" Then
Leftover = brkout(x)
Else
Leftover = Leftover & Delemiter & brkout(x)
End If
Next
End If
End Sub

Regards,
Per

 

[Nigel]

Couple of observations first. I presume you are setting option base to 1 as
you show B = brkout(1)
also I presume that after the first four strings there is two ellipses (..)
otherwise the text would remain as part of the fourth string. So try this
to strip everything past those final ellipses.

leftover = Mid(A, 8 + Len(B) + Len(C) + Len(D) + Len(E))

 

[Jacob Skaria]

Try this..

Dim strA As String
Dim strB As String
Dim strC As String
Dim strD As String
Dim strE As String
Dim arrTemp As Variant

arrTemp = Split("1..2..3..4..5..6..7..", "..")
strA = arrTemp(0)
strB = arrTemp(1)
strC = arrTemp(2)
strD = arrTemp(3)

For intTemp = 4 To UBound(arrTemp)
'If you dont want to store delimiter remove that...
strE = strE & arrTemp(intTemp) & ".."
Next

 

[Rick Rothstein]

Will you always have enough text to fill B, C, D, E and Leftover? Assuming
you will, use the optional 3rd argument for the Split function to control
the number of substrings being formed. For example...

Dim A As String
Dim brkout() As String
Dim B As String, C As String, D As String, E As String, Leftover As String

A = "This..Is..A..Long..Example..Test..String"

brkout= Split(A, "..", 5)
B = brkout(0)
C = brkout(1)
D = brkout(2)
E = brkout(3)
Leftover = brkout(4)

 

[Rick Rothstein]

I presume you are setting option base to 1 as you show B = brkout(1)

The Option Base doesn't matter... the Split function **always** returns a
zero-based array, no matter what the Option Base is set to.

 

[Nigel]

OK thanks for that, I thought the OP was referring to possibly the wrong
element. So he does need to uses brkout(0) as the first string.

 

[jay dean]

Thanks a lot, Per Jessen, Nigel, Jacob, and Rick.
@ Jacob -- My approach was exactly like yours, except your For-loop
construct is more efficient. I see in your code, strE was not
initialized first. In VBA, are all string declarations automatically set
to null in the beginning?

@ Rick -- Your bringing up of the optional 3rd argument gave me another
new idea for implementing another part of my project.

@Nigel and Per Jessen -- Yes, you were right. I should have probably
stated in my original post that my first index was brkout(i)=0, not
brkout(i)=1. I sometimes forget VBA indices start from 0.

Jay

 

[Rick Rothstein]

Just out of curiosity, why wouldn't you use the 3rd argument approach for
this part of your application as well (given that it eliminates the need for
a loop altogether)?

 

[Nigel]

Rick your solution is most elegant.

--

Regards,
Nigel
(e-mail address removed)

 

[jay dean]

Actually, I now see that your code will be the one to use because the
3rd argument specifies "how many strings to return". With my needing the
first 4, everything else will be stored in the 5th (Leftover).

Man, the SPLIT() function is really powerful. Thanks again, Rick !


Jay

==============================================
Rick wrote:
Just out of curiosity, why wouldn't you use the 3rd argument approach
for
this part of your application as well (given that it eliminates the need
for
a loop altogether)?

========================================

 

[Jacob Skaria]

From the original query I wan't sure whether Jay would want the data to be
splitted and stored in the last variable..

The 3rd argument of SPLIT() takes a by default value of -1 which indicate to
split all substrings..and the 4th argument gives an option to specify the
comparison (binary or text) which make is powerful.

Thanks Rick.

If this post helps click Yes