Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! ;) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Substituting substrings | Excel Programming | |||
Macro Help for Substrings | Excel Programming | |||
sum wrt substrings! | Excel Worksheet Functions | |||
Substrings in Excel? | Excel Discussion (Misc queries) | |||
STRINGS AND SUBSTRINGS ! | Excel Programming |