Parse substrings
 

My input string is a set of numbers that represent prices.
Each price is separated by one or more spaces AND/OR tabs.
For example:

s = " 1200 300 25 2 4 10000 "

There might also be tabs separating numbers, such as:

s = " 1200[tab]300 25 2 4[tab][tab]1000 "

where [tab] is the location of an actual tab.

Assuming that my data will always have the following format,
what is a good way to extract each numerical value into
a variant or array?

I'm thinking the Split() function will help, but I'm kind of
thrown off because the separators could be spaces AND/OR tabs.

-Robert

Parse substrings
 

RG III wrote:

My input string is a set of numbers that represent prices.
Each price is separated by one or more spaces AND/OR tabs.
For example:

s = " 1200 300 25 2 4 10000 "

There might also be tabs separating numbers, such as:

s = " 1200[tab]300 25 2 4[tab][tab]1000 "

where [tab] is the location of an actual tab.

Assuming that my data will always have the following format,
what is a good way to extract each numerical value into
a variant or array?

I'm thinking the Split() function will help, but I'm kind of
thrown off because the separators could be spaces AND/OR tabs.

Something like this? (Untested air code.)

Dim s1 As String, s2 As String, s3 As Variant
s1 = Trim(Replace(s, vbTab, " "))
Do
s2 = s1
s1 = Replace(s2, " ", " ")
Loop While Len(s1) < Len(s2)
s3 = Split(s1)

--
Perhaps. But allow me to shed some light on an alternate hypothesis.

Parse substrings
 

Hi Robert,

Am Thu, 10 Oct 2019 02:43:53 -0700 (PDT) schrieb RG III:

My input string is a set of numbers that represent prices.
Each price is separated by one or more spaces AND/OR tabs.
For example:

s = " 1200 300 25 2 4 10000 "

There might also be tabs separating numbers, such as:

s = " 1200[tab]300 25 2 4[tab][tab]1000 "

you also can try:

Sub Test()
Dim s As String
Dim varMatches As Variant

s = " 1200 " & vbTab & " 300 25 2 4" & vbTab & vbTab & "1000 "

s = Replace(s, vbTab, " ")
s = Application.Trim(s)
varMatches = Split(s, " ")
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Parse substrings
 

Hi Robert,

Am Thu, 10 Oct 2019 12:54:25 +0200 schrieb Claus Busch:

Sub Test()
Dim s As String
Dim varMatches As Variant

another way is to use regex:

Sub Test()
Dim s As String
Dim re As Object, varMatches As Variant

s = " 1200 " & vbTab & " 300 25 2 4" & vbTab & vbTab & "1000 "

Set re = CreateObject("vbscript.regexp")
re.Pattern = "\d+\b"
re.Global = True
Set varMatches = re.Execute(s)
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Parse substrings
 

Something like this? (Untested air code.)

Dim s1 As String, s2 As String, s3 As Variant
s1 = Trim(Replace(s, vbTab, " "))
Do
s2 = s1
s1 = Replace(s2, " ", " ")
Loop While Len(s1) < Len(s2)
s3 = Split(s1)


Yes! The air code works fine! Thank you Air Master! ;)

Parse substrings
 

Sub Test()
Dim s As String
Dim varMatches As Variant

s = " 1200 " & vbTab & " 300 25 2 4" & vbTab & vbTab & "1000 "

s = Replace(s, vbTab, " ")
s = Application.Trim(s)
varMatches = Split(s, " ")
End Sub


Yes! I like this solution because it's short and sweet, and the built-in functions do most of the work.

Thank you yet again Claus!