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):D=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


*** Sent via Developersdex http://www.developersdex.com ***

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

On 8 Maj, 08:22, jay dean wrote:
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):D=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

*** Sent via Developersdexhttp://www.developersdex.com***

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))


--

Regards,
Nigel




"jay dean" wrote in message
...

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):D=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


*** Sent via Developersdex http://www.developersdex.com ***

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

--
If this post helps click Yes
---------------
Jacob Skaria


"jay dean" wrote:


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):D=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


*** Sent via Developersdex http://www.developersdex.com ***

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 (MVP - Excel)


"jay dean" wrote in message
...

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):D=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


*** Sent via Developersdex http://www.developersdex.com ***

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.

--
Rick (MVP - Excel)


"Nigel" wrote in message
...
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))


--

Regards,
Nigel




"jay dean" wrote in message
...

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):D=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


*** Sent via Developersdex http://www.developersdex.com ***

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.

--
Regards,
Nigel




"Rick Rothstein" wrote in message
...
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.

--
Rick (MVP - Excel)


"Nigel" wrote in message
...
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))


--

Regards,
Nigel




"jay dean" wrote in message
...

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):D=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


*** Sent via Developersdex http://www.developersdex.com ***

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

*** Sent via Developersdex http://www.developersdex.com ***

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)?

--
Rick (MVP - Excel)


"jay dean" wrote in message
...
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

*** Sent via Developersdex http://www.developersdex.com ***

Rick your solution is most elegant.

--

Regards,
Nigel




"Rick Rothstein" wrote in message
...
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)?

--
Rick (MVP - Excel)


"jay dean" wrote in message
...
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

*** Sent via Developersdex http://www.developersdex.com ***

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)?

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



*** Sent via Developersdex http://www.developersdex.com ***

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
---------------
Jacob Skaria


"Rick Rothstein" 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)?

--
Rick (MVP - Excel)


"jay dean" wrote in message
...
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

*** Sent via Developersdex http://www.developersdex.com ***