splitting strings

I have multiple strings like the following...

"ITEM 1000 20 X4"

I want to split the string into an array. So I did the following...

tmp = split(line)

Now this is the array data...

tmp[0] = "ITEM"
tmp[1] = " "
tmp[2] = " "
tmp[3] = "100"
tmp[4] = " "
...and so on.

I want to get rid of the blank array points; tmp[1], tmp[2], tmp[4] such that the array will read like this...

tmp[0] = "ITEM"
tmp[1] = "100"
tmp[2] = "20"
...and so on.

Is there a way to split strings on "white space". I only ask because the strings I will be digesting are of variable length. I can program a do-while-loop to count the number of blank entries and work around, but I figured there had to be an easier way to do that.

THANKS TONS
-Todd

You have a variable number of spaces between the words; you need to replace
multiple space separators by a single space and then use that space to split.
What happened to X4?

function SplitSen(ByVAl Sen as string)
do until 0<>instr(Sen," ") ' 2 spaces within quotes
Sen = erplace(sen," "," ")
loop
SplitSen = split(sen," ")
end function

"tad_wegner" wrote:

>
> I have multiple strings like the following...
>
> "ITEM 1000 20 X4"
>
> I want to split the string into an array. So I did the following...
>
> tmp = split(line)
>
> Now this is the array data...
>
> tmp[0] = "ITEM"
> tmp[1] = " "
> tmp[2] = " "
> tmp[3] = "100"
> tmp[4] = " "
> ...and so on.
>
> I want to get rid of the blank array points; tmp[1], tmp[2], tmp[4]
> such that the array will read like this...
>
> tmp[0] = "ITEM"
> tmp[1] = "100"
> tmp[2] = "20"
> ...and so on.
>
> Is there a way to split strings on "white space". I only ask because
> the strings I will be digesting are of variable length. I can program a
> do-while-loop to count the number of blank entries and work around, but
> I figured there had to be an easier way to do that.
>
> THANKS TONS
> -Todd
>
>
> --
> tad_wegner
> ------------------------------------------------------------------------
> tad_wegner's Profile: http://www.excelforum.com/member.php...o&userid=27770
> View this thread: http://www.excelforum.com/showthread...hreadid=536619
>
>

It is a Do While loop, but can't see much wrong with it.

Sub test()

Dim str As String
Dim i As Long
Dim arr

str = "ITEM 1000 20 X4"

Do While InStr(1, str, " ", vbBinaryCompare) > 0
str = Replace(str, " ", " ", 1, -1, vbBinaryCompare)
Loop

arr = Split(str, " ")

For i = 0 To UBound(arr)
MsgBox arr(i)
Next

End Sub


RBS


"tad_wegner" <[email protected]> wrote
in message news:[email protected]...
>
> I have multiple strings like the following...
>
> "ITEM 1000 20 X4"
>
> I want to split the string into an array. So I did the following...
>
> tmp = split(line)
>
> Now this is the array data...
>
> tmp[0] = "ITEM"
> tmp[1] = " "
> tmp[2] = " "
> tmp[3] = "100"
> tmp[4] = " "
> ..and so on.
>
> I want to get rid of the blank array points; tmp[1], tmp[2], tmp[4]
> such that the array will read like this...
>
> tmp[0] = "ITEM"
> tmp[1] = "100"
> tmp[2] = "20"
> ..and so on.
>
> Is there a way to split strings on "white space". I only ask because
> the strings I will be digesting are of variable length. I can program a
> do-while-loop to count the number of blank entries and work around, but
> I figured there had to be an easier way to do that.
>
> THANKS TONS
> -Todd
>
>
> --
> tad_wegner
> ------------------------------------------------------------------------
> tad_wegner's Profile:
> http://www.excelforum.com/member.php...o&userid=27770
> View this thread: http://www.excelforum.com/showthread...hreadid=536619
>

How about using worksheet function trim like this

tmp = split(Application.Trim(line))

keizi

"tad_wegner" <[email protected]>
wrote in message
news:[email protected]...
>
> I have multiple strings like the following...
>
> "ITEM 1000 20 X4"
>
> I want to split the string into an array. So I did the following...
>
> tmp = split(line)
>
> Now this is the array data...
>
> tmp[0] = "ITEM"
> tmp[1] = " "
> tmp[2] = " "
> tmp[3] = "100"
> tmp[4] = " "
> ..and so on.
>
> I want to get rid of the blank array points; tmp[1], tmp[2], tmp[4]
> such that the array will read like this...
>
> tmp[0] = "ITEM"
> tmp[1] = "100"
> tmp[2] = "20"
> ..and so on.
>
> Is there a way to split strings on "white space". I only ask because
> the strings I will be digesting are of variable length. I can program
a
> do-while-loop to count the number of blank entries and work around,
but
> I figured there had to be an easier way to do that.
>
> THANKS TONS
> -Todd
>
>
> --
> tad_wegner
> ----------------------------------------------------------------------
--
> tad_wegner's Profile:
http://www.excelforum.com/member.php...o&userid=27770
> View this thread:
http://www.excelforum.com/showthread...hreadid=536619
>

VBA has a Trim() function--but it leaves multiple embedded spaces intact.

But you can use application.trim(). That cleans up the leading/trailing spaces
(just like VBA's Trim()), but it also eliminates those multiple embedded
spaces--just leaving one when it's done:

Option Explicit
Sub testme01()

Dim myStr As String
Dim mySplit As Variant
Dim iCtr As Long

myStr = "ITEM 1000 20 X4"
myStr = Application.Trim(myStr)

mySplit = Split(myStr, " ")
For iCtr = LBound(mySplit) To UBound(mySplit)
MsgBox iCtr & "--" & mySplit(iCtr)
Next iCtr
End Sub



tad_wegner wrote:
>
> I have multiple strings like the following...
>
> "ITEM 1000 20 X4"
>
> I want to split the string into an array. So I did the following...
>
> tmp = split(line)
>
> Now this is the array data...
>
> tmp[0] = "ITEM"
> tmp[1] = " "
> tmp[2] = " "
> tmp[3] = "100"
> tmp[4] = " "
> ..and so on.
>
> I want to get rid of the blank array points; tmp[1], tmp[2], tmp[4]
> such that the array will read like this...
>
> tmp[0] = "ITEM"
> tmp[1] = "100"
> tmp[2] = "20"
> ..and so on.
>
> Is there a way to split strings on "white space". I only ask because
> the strings I will be digesting are of variable length. I can program a
> do-while-loop to count the number of blank entries and work around, but
> I figured there had to be an easier way to do that.
>
> THANKS TONS
> -Todd
>
> --
> tad_wegner
> ------------------------------------------------------------------------
> tad_wegner's Profile: http://www.excelforum.com/member.php...o&userid=27770
> View this thread: http://www.excelforum.com/showthread...hreadid=536619

--

Dave Peterson

split(sLine," ") 'using however many spaces there are.

Split doesn't require you to split on a single character: you can use any string value.

Tim.



"tad_wegner" <[email protected]> wrote in message
news:[email protected]...
>
> I have multiple strings like the following...
>
> "ITEM 1000 20 X4"
>
> I want to split the string into an array. So I did the following...
>
> tmp = split(line)
>
> Now this is the array data...
>
> tmp[0] = "ITEM"
> tmp[1] = " "
> tmp[2] = " "
> tmp[3] = "100"
> tmp[4] = " "
> ..and so on.
>
> I want to get rid of the blank array points; tmp[1], tmp[2], tmp[4]
> such that the array will read like this...
>
> tmp[0] = "ITEM"
> tmp[1] = "100"
> tmp[2] = "20"
> ..and so on.
>
> Is there a way to split strings on "white space". I only ask because
> the strings I will be digesting are of variable length. I can program a
> do-while-loop to count the number of blank entries and work around, but
> I figured there had to be an easier way to do that.
>
> THANKS TONS
> -Todd
>
>
> --
> tad_wegner
> ------------------------------------------------------------------------
> tad_wegner's Profile: http://www.excelforum.com/member.php...o&userid=27770
> View this thread: http://www.excelforum.com/showthread...hreadid=536619
>

That is a nice one, thanks for the tip.
Replace in a Do While loop is about 50% faster though.

RBS

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> VBA has a Trim() function--but it leaves multiple embedded spaces intact.
>
> But you can use application.trim(). That cleans up the leading/trailing
> spaces
> (just like VBA's Trim()), but it also eliminates those multiple embedded
> spaces--just leaving one when it's done:
>
> Option Explicit
> Sub testme01()
>
> Dim myStr As String
> Dim mySplit As Variant
> Dim iCtr As Long
>
> myStr = "ITEM 1000 20 X4"
> myStr = Application.Trim(myStr)
>
> mySplit = Split(myStr, " ")
> For iCtr = LBound(mySplit) To UBound(mySplit)
> MsgBox iCtr & "--" & mySplit(iCtr)
> Next iCtr
> End Sub
>
>
>
> tad_wegner wrote:
>>
>> I have multiple strings like the following...
>>
>> "ITEM 1000 20 X4"
>>
>> I want to split the string into an array. So I did the following...
>>
>> tmp = split(line)
>>
>> Now this is the array data...
>>
>> tmp[0] = "ITEM"
>> tmp[1] = " "
>> tmp[2] = " "
>> tmp[3] = "100"
>> tmp[4] = " "
>> ..and so on.
>>
>> I want to get rid of the blank array points; tmp[1], tmp[2], tmp[4]
>> such that the array will read like this...
>>
>> tmp[0] = "ITEM"
>> tmp[1] = "100"
>> tmp[2] = "20"
>> ..and so on.
>>
>> Is there a way to split strings on "white space". I only ask because
>> the strings I will be digesting are of variable length. I can program a
>> do-while-loop to count the number of blank entries and work around, but
>> I figured there had to be an easier way to do that.
>>
>> THANKS TONS
>> -Todd
>>
>> --
>> tad_wegner
>> ------------------------------------------------------------------------
>> tad_wegner's Profile:
>> http://www.excelforum.com/member.php...o&userid=27770
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=536619
>
> --
>
> Dave Peterson

Yes, that is another option, but it has the same speed as Application.Trim
and
can't see any benefit in this particular case.

RBS


"jindon" <[email protected]> wrote in
message news:[email protected]...
>
> Clean the data useing Regualr Expression
>
>
> Code:
> --------------------
>
> Sub test()
> Dim Line As String
> Line = "ITEM 1000 20 X4 "
> With CreateObject("VBScript.RegExp")
> .Pattern = "\s{2,}"
> .Global = True
> Line = .Replace(Line, Chr(32))
> End With
> s = Split(Line)
> End Sub
>
> --------------------
>
>
> --
> jindon
> ------------------------------------------------------------------------
> jindon's Profile:
> http://www.excelforum.com/member.php...o&userid=13135
> View this thread: http://www.excelforum.com/showthread...hreadid=536619
>

but watch out for strings like:

str = " ITEM 1000 20 X4 "

I would expect overhead using the worksheet function, but I'm betting that
application.trim() will look better when the number of spaces between elements
and the total number of elements gets large.

(No, I didn't test any of that theory!)


RB Smissaert wrote:
>
> It is a Do While loop, but can't see much wrong with it.
>
> Sub test()
>
> Dim str As String
> Dim i As Long
> Dim arr
>
> str = "ITEM 1000 20 X4"
>
> Do While InStr(1, str, " ", vbBinaryCompare) > 0
> str = Replace(str, " ", " ", 1, -1, vbBinaryCompare)
> Loop
>
> arr = Split(str, " ")
>
> For i = 0 To UBound(arr)
> MsgBox arr(i)
> Next
>
> End Sub
>
> RBS
>
> "tad_wegner" <[email protected]> wrote
> in message news:[email protected]...
> >
> > I have multiple strings like the following...
> >
> > "ITEM 1000 20 X4"
> >
> > I want to split the string into an array. So I did the following...
> >
> > tmp = split(line)
> >
> > Now this is the array data...
> >
> > tmp[0] = "ITEM"
> > tmp[1] = " "
> > tmp[2] = " "
> > tmp[3] = "100"
> > tmp[4] = " "
> > ..and so on.
> >
> > I want to get rid of the blank array points; tmp[1], tmp[2], tmp[4]
> > such that the array will read like this...
> >
> > tmp[0] = "ITEM"
> > tmp[1] = "100"
> > tmp[2] = "20"
> > ..and so on.
> >
> > Is there a way to split strings on "white space". I only ask because
> > the strings I will be digesting are of variable length. I can program a
> > do-while-loop to count the number of blank entries and work around, but
> > I figured there had to be an easier way to do that.
> >
> > THANKS TONS
> > -Todd
> >
> >
> > --
> > tad_wegner
> > ------------------------------------------------------------------------
> > tad_wegner's Profile:
> > http://www.excelforum.com/member.php...o&userid=27770
> > View this thread: http://www.excelforum.com/showthread...hreadid=536619
> >

--

Dave Peterson