splitting strings

[tad_wegner]

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

 

[AA2e72E]

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

 

[RB Smissaert]

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

 

[kounoike]

How about using worksheet function trim like this

tmp = split(Application.Trim(line))

keizi

"tad_wegner" <[email protected]>
wrote in message
news:[email protected]...

 

[Dave Peterson]

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

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

 

[Tim Williams]

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.

 

[RB Smissaert]

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

RBS

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

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?action=getinfo&userid=27770
View this thread:
http://www.excelforum.com/showthread.php?threadid=536619

 

[jindon]

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

 

[RB Smissaert]

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

 

[Dave Peterson]

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

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

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