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 becaus
the strings I will be digesting are of variable length. I can program
do-while-loop to count the number of blank entries and work around, bu
I figured there had to be an easier way to do that.

THANKS TONS
-Tod

--
tad_wegne
-----------------------------------------------------------------------
tad_wegner's Profile: http://www.excelforum.com/member.php...fo&userid=2777
View this thread: http://www.excelforum.com/showthread.php?threadid=53661

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

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

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

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

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

-------------------

--
jindo
-----------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...fo&userid=1313
View this thread: http://www.excelforum.com/showthread.php?threadid=53661

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

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

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