How to split a string in VBA to array by Split function delimited by Regular Expression












1















I am writing an Excel Add In to read a text file, extract values and write them to an Excel file. I need to split a line, delimited by one or more white spaces and store it in the form of array, from which I want to extract desired values.



I am trying to implement something like this:



arrStr = Split(line, "/^s*/")


But the editor is throwing an error while compiling.



How can I do what I want?










share|improve this question

























  • stackoverflow.com/questions/28107005/…

    – Tim Williams
    Nov 18 '18 at 6:12











  • Split doesn't do that. You'll need to find another way. Why the arbitrary "one line" requirement? Reflex could dot it in a few lines

    – chris neilsen
    Nov 18 '18 at 6:13






  • 1





    You're still not going to be able to do that with the Split() function. Question, is this truly VBScript as you've tagged? Or are you actually using vba "Visual Basic for Applications"? VBA is what you get when you press Alt + F11 inside Excel - not VBScript.

    – K.Dᴀᴠɪs
    Nov 18 '18 at 6:27






  • 1





    Thanks for the clarification - could you please edit your question and update the correct tag so you reach the correct audience?

    – K.Dᴀᴠɪs
    Nov 18 '18 at 6:32






  • 1





    split doesn't do RegEx.

    – CatCat
    Nov 18 '18 at 6:45
















1















I am writing an Excel Add In to read a text file, extract values and write them to an Excel file. I need to split a line, delimited by one or more white spaces and store it in the form of array, from which I want to extract desired values.



I am trying to implement something like this:



arrStr = Split(line, "/^s*/")


But the editor is throwing an error while compiling.



How can I do what I want?










share|improve this question

























  • stackoverflow.com/questions/28107005/…

    – Tim Williams
    Nov 18 '18 at 6:12











  • Split doesn't do that. You'll need to find another way. Why the arbitrary "one line" requirement? Reflex could dot it in a few lines

    – chris neilsen
    Nov 18 '18 at 6:13






  • 1





    You're still not going to be able to do that with the Split() function. Question, is this truly VBScript as you've tagged? Or are you actually using vba "Visual Basic for Applications"? VBA is what you get when you press Alt + F11 inside Excel - not VBScript.

    – K.Dᴀᴠɪs
    Nov 18 '18 at 6:27






  • 1





    Thanks for the clarification - could you please edit your question and update the correct tag so you reach the correct audience?

    – K.Dᴀᴠɪs
    Nov 18 '18 at 6:32






  • 1





    split doesn't do RegEx.

    – CatCat
    Nov 18 '18 at 6:45














1












1








1








I am writing an Excel Add In to read a text file, extract values and write them to an Excel file. I need to split a line, delimited by one or more white spaces and store it in the form of array, from which I want to extract desired values.



I am trying to implement something like this:



arrStr = Split(line, "/^s*/")


But the editor is throwing an error while compiling.



How can I do what I want?










share|improve this question
















I am writing an Excel Add In to read a text file, extract values and write them to an Excel file. I need to split a line, delimited by one or more white spaces and store it in the form of array, from which I want to extract desired values.



I am trying to implement something like this:



arrStr = Split(line, "/^s*/")


But the editor is throwing an error while compiling.



How can I do what I want?







regex excel vba excel-addins






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 19 '18 at 19:49









K.Dᴀᴠɪs

7,159112339




7,159112339










asked Nov 18 '18 at 6:00









kush.impetuskush.impetus

1,09262335




1,09262335













  • stackoverflow.com/questions/28107005/…

    – Tim Williams
    Nov 18 '18 at 6:12











  • Split doesn't do that. You'll need to find another way. Why the arbitrary "one line" requirement? Reflex could dot it in a few lines

    – chris neilsen
    Nov 18 '18 at 6:13






  • 1





    You're still not going to be able to do that with the Split() function. Question, is this truly VBScript as you've tagged? Or are you actually using vba "Visual Basic for Applications"? VBA is what you get when you press Alt + F11 inside Excel - not VBScript.

    – K.Dᴀᴠɪs
    Nov 18 '18 at 6:27






  • 1





    Thanks for the clarification - could you please edit your question and update the correct tag so you reach the correct audience?

    – K.Dᴀᴠɪs
    Nov 18 '18 at 6:32






  • 1





    split doesn't do RegEx.

    – CatCat
    Nov 18 '18 at 6:45



















  • stackoverflow.com/questions/28107005/…

    – Tim Williams
    Nov 18 '18 at 6:12











  • Split doesn't do that. You'll need to find another way. Why the arbitrary "one line" requirement? Reflex could dot it in a few lines

    – chris neilsen
    Nov 18 '18 at 6:13






  • 1





    You're still not going to be able to do that with the Split() function. Question, is this truly VBScript as you've tagged? Or are you actually using vba "Visual Basic for Applications"? VBA is what you get when you press Alt + F11 inside Excel - not VBScript.

    – K.Dᴀᴠɪs
    Nov 18 '18 at 6:27






  • 1





    Thanks for the clarification - could you please edit your question and update the correct tag so you reach the correct audience?

    – K.Dᴀᴠɪs
    Nov 18 '18 at 6:32






  • 1





    split doesn't do RegEx.

    – CatCat
    Nov 18 '18 at 6:45

















stackoverflow.com/questions/28107005/…

– Tim Williams
Nov 18 '18 at 6:12





stackoverflow.com/questions/28107005/…

– Tim Williams
Nov 18 '18 at 6:12













Split doesn't do that. You'll need to find another way. Why the arbitrary "one line" requirement? Reflex could dot it in a few lines

– chris neilsen
Nov 18 '18 at 6:13





Split doesn't do that. You'll need to find another way. Why the arbitrary "one line" requirement? Reflex could dot it in a few lines

– chris neilsen
Nov 18 '18 at 6:13




1




1





You're still not going to be able to do that with the Split() function. Question, is this truly VBScript as you've tagged? Or are you actually using vba "Visual Basic for Applications"? VBA is what you get when you press Alt + F11 inside Excel - not VBScript.

– K.Dᴀᴠɪs
Nov 18 '18 at 6:27





You're still not going to be able to do that with the Split() function. Question, is this truly VBScript as you've tagged? Or are you actually using vba "Visual Basic for Applications"? VBA is what you get when you press Alt + F11 inside Excel - not VBScript.

– K.Dᴀᴠɪs
Nov 18 '18 at 6:27




1




1





Thanks for the clarification - could you please edit your question and update the correct tag so you reach the correct audience?

– K.Dᴀᴠɪs
Nov 18 '18 at 6:32





Thanks for the clarification - could you please edit your question and update the correct tag so you reach the correct audience?

– K.Dᴀᴠɪs
Nov 18 '18 at 6:32




1




1





split doesn't do RegEx.

– CatCat
Nov 18 '18 at 6:45





split doesn't do RegEx.

– CatCat
Nov 18 '18 at 6:45












1 Answer
1






active

oldest

votes


















4














If you are looking for the Regular Expressions route, then you could do something like this:



Dim line As String, arrStr, i As Long
line = "This is a test"

With New RegExp
.Pattern = "S+"
.Global = True
If .test(line) Then
With .Execute(line)
ReDim arrStr(.Count - 1)
For i = 0 To .Count - 1
arrStr(i) = .Item(i)
Next
End With
End If
End With



IMPORTANT: You will need to create a reference to:
Microsoft VBScript Regular Expressions 5.5 in Tools > References

Otherwise, you can see Late Binding below




Your original implementation of your original pattern ^S*$ had some issues:





  • S* was actually matching a literal uppercase S, not the whitespace character you were looking for - because it was not escaped.


    • Even if it was escaped, you would have matched every string that you used because of your quantifier: * means to match zero or more of S. You were probably looking for the + quantifier (one or more of).

    • You were good for making it greedy (not using *?) since you were wanting to consume as much as possible.




The Pattern I used: (S+) is placed in a capturing group (...) that will capture all cases of S+ (all characters that are NOT a white space, + one or more times.



I also used the .Global so you will continue matching after the first match.



Once you have captured all your words, you can then loop through the match collection and place them into an array.





Late Binding:



Dim line As String, arrStr, i As Long
line = "This is a test"

With CreateObject("VBScript.RegExp")
.Pattern = "S+"
.Global = True
If .test(line) Then
With .Execute(line)
ReDim arrStr(.Count - 1)
For i = 0 To .Count - 1
arrStr(i) = .Item(i)
Next
End With
End If
End With




Miscellaneous Notes



I would have advised just to use Split(), but you stated that there were cases where more than one consecutive space may have been an issue. If this wasn't the case, you wouldn't need regex at all, something like:



arrStr = Split(line)


Would have split on every occurance of a space






share|improve this answer


























  • The code with Late Binding worked for me.

    – kush.impetus
    Nov 18 '18 at 7:25






  • 2





    @kush.impetus If you remove the redundant capturing group it will be cleaner: .Pattern = "S+" and arrStr(i) = .Item(i) (there won't be any need to access Submatches(0))

    – Wiktor Stribiżew
    Nov 18 '18 at 10:04













  • @WiktorStribiżew thanks for pointing that out - that was leftover as part of a different route I was taking. I corrected it :)

    – K.Dᴀᴠɪs
    Nov 18 '18 at 10:09













  • @K.Davis. Thanks. Your code has made my task lot easier.

    – kush.impetus
    Nov 19 '18 at 13:10











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53358309%2fhow-to-split-a-string-in-vba-to-array-by-split-function-delimited-by-regular-exp%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









4














If you are looking for the Regular Expressions route, then you could do something like this:



Dim line As String, arrStr, i As Long
line = "This is a test"

With New RegExp
.Pattern = "S+"
.Global = True
If .test(line) Then
With .Execute(line)
ReDim arrStr(.Count - 1)
For i = 0 To .Count - 1
arrStr(i) = .Item(i)
Next
End With
End If
End With



IMPORTANT: You will need to create a reference to:
Microsoft VBScript Regular Expressions 5.5 in Tools > References

Otherwise, you can see Late Binding below




Your original implementation of your original pattern ^S*$ had some issues:





  • S* was actually matching a literal uppercase S, not the whitespace character you were looking for - because it was not escaped.


    • Even if it was escaped, you would have matched every string that you used because of your quantifier: * means to match zero or more of S. You were probably looking for the + quantifier (one or more of).

    • You were good for making it greedy (not using *?) since you were wanting to consume as much as possible.




The Pattern I used: (S+) is placed in a capturing group (...) that will capture all cases of S+ (all characters that are NOT a white space, + one or more times.



I also used the .Global so you will continue matching after the first match.



Once you have captured all your words, you can then loop through the match collection and place them into an array.





Late Binding:



Dim line As String, arrStr, i As Long
line = "This is a test"

With CreateObject("VBScript.RegExp")
.Pattern = "S+"
.Global = True
If .test(line) Then
With .Execute(line)
ReDim arrStr(.Count - 1)
For i = 0 To .Count - 1
arrStr(i) = .Item(i)
Next
End With
End If
End With




Miscellaneous Notes



I would have advised just to use Split(), but you stated that there were cases where more than one consecutive space may have been an issue. If this wasn't the case, you wouldn't need regex at all, something like:



arrStr = Split(line)


Would have split on every occurance of a space






share|improve this answer


























  • The code with Late Binding worked for me.

    – kush.impetus
    Nov 18 '18 at 7:25






  • 2





    @kush.impetus If you remove the redundant capturing group it will be cleaner: .Pattern = "S+" and arrStr(i) = .Item(i) (there won't be any need to access Submatches(0))

    – Wiktor Stribiżew
    Nov 18 '18 at 10:04













  • @WiktorStribiżew thanks for pointing that out - that was leftover as part of a different route I was taking. I corrected it :)

    – K.Dᴀᴠɪs
    Nov 18 '18 at 10:09













  • @K.Davis. Thanks. Your code has made my task lot easier.

    – kush.impetus
    Nov 19 '18 at 13:10
















4














If you are looking for the Regular Expressions route, then you could do something like this:



Dim line As String, arrStr, i As Long
line = "This is a test"

With New RegExp
.Pattern = "S+"
.Global = True
If .test(line) Then
With .Execute(line)
ReDim arrStr(.Count - 1)
For i = 0 To .Count - 1
arrStr(i) = .Item(i)
Next
End With
End If
End With



IMPORTANT: You will need to create a reference to:
Microsoft VBScript Regular Expressions 5.5 in Tools > References

Otherwise, you can see Late Binding below




Your original implementation of your original pattern ^S*$ had some issues:





  • S* was actually matching a literal uppercase S, not the whitespace character you were looking for - because it was not escaped.


    • Even if it was escaped, you would have matched every string that you used because of your quantifier: * means to match zero or more of S. You were probably looking for the + quantifier (one or more of).

    • You were good for making it greedy (not using *?) since you were wanting to consume as much as possible.




The Pattern I used: (S+) is placed in a capturing group (...) that will capture all cases of S+ (all characters that are NOT a white space, + one or more times.



I also used the .Global so you will continue matching after the first match.



Once you have captured all your words, you can then loop through the match collection and place them into an array.





Late Binding:



Dim line As String, arrStr, i As Long
line = "This is a test"

With CreateObject("VBScript.RegExp")
.Pattern = "S+"
.Global = True
If .test(line) Then
With .Execute(line)
ReDim arrStr(.Count - 1)
For i = 0 To .Count - 1
arrStr(i) = .Item(i)
Next
End With
End If
End With




Miscellaneous Notes



I would have advised just to use Split(), but you stated that there were cases where more than one consecutive space may have been an issue. If this wasn't the case, you wouldn't need regex at all, something like:



arrStr = Split(line)


Would have split on every occurance of a space






share|improve this answer


























  • The code with Late Binding worked for me.

    – kush.impetus
    Nov 18 '18 at 7:25






  • 2





    @kush.impetus If you remove the redundant capturing group it will be cleaner: .Pattern = "S+" and arrStr(i) = .Item(i) (there won't be any need to access Submatches(0))

    – Wiktor Stribiżew
    Nov 18 '18 at 10:04













  • @WiktorStribiżew thanks for pointing that out - that was leftover as part of a different route I was taking. I corrected it :)

    – K.Dᴀᴠɪs
    Nov 18 '18 at 10:09













  • @K.Davis. Thanks. Your code has made my task lot easier.

    – kush.impetus
    Nov 19 '18 at 13:10














4












4








4







If you are looking for the Regular Expressions route, then you could do something like this:



Dim line As String, arrStr, i As Long
line = "This is a test"

With New RegExp
.Pattern = "S+"
.Global = True
If .test(line) Then
With .Execute(line)
ReDim arrStr(.Count - 1)
For i = 0 To .Count - 1
arrStr(i) = .Item(i)
Next
End With
End If
End With



IMPORTANT: You will need to create a reference to:
Microsoft VBScript Regular Expressions 5.5 in Tools > References

Otherwise, you can see Late Binding below




Your original implementation of your original pattern ^S*$ had some issues:





  • S* was actually matching a literal uppercase S, not the whitespace character you were looking for - because it was not escaped.


    • Even if it was escaped, you would have matched every string that you used because of your quantifier: * means to match zero or more of S. You were probably looking for the + quantifier (one or more of).

    • You were good for making it greedy (not using *?) since you were wanting to consume as much as possible.




The Pattern I used: (S+) is placed in a capturing group (...) that will capture all cases of S+ (all characters that are NOT a white space, + one or more times.



I also used the .Global so you will continue matching after the first match.



Once you have captured all your words, you can then loop through the match collection and place them into an array.





Late Binding:



Dim line As String, arrStr, i As Long
line = "This is a test"

With CreateObject("VBScript.RegExp")
.Pattern = "S+"
.Global = True
If .test(line) Then
With .Execute(line)
ReDim arrStr(.Count - 1)
For i = 0 To .Count - 1
arrStr(i) = .Item(i)
Next
End With
End If
End With




Miscellaneous Notes



I would have advised just to use Split(), but you stated that there were cases where more than one consecutive space may have been an issue. If this wasn't the case, you wouldn't need regex at all, something like:



arrStr = Split(line)


Would have split on every occurance of a space






share|improve this answer















If you are looking for the Regular Expressions route, then you could do something like this:



Dim line As String, arrStr, i As Long
line = "This is a test"

With New RegExp
.Pattern = "S+"
.Global = True
If .test(line) Then
With .Execute(line)
ReDim arrStr(.Count - 1)
For i = 0 To .Count - 1
arrStr(i) = .Item(i)
Next
End With
End If
End With



IMPORTANT: You will need to create a reference to:
Microsoft VBScript Regular Expressions 5.5 in Tools > References

Otherwise, you can see Late Binding below




Your original implementation of your original pattern ^S*$ had some issues:





  • S* was actually matching a literal uppercase S, not the whitespace character you were looking for - because it was not escaped.


    • Even if it was escaped, you would have matched every string that you used because of your quantifier: * means to match zero or more of S. You were probably looking for the + quantifier (one or more of).

    • You were good for making it greedy (not using *?) since you were wanting to consume as much as possible.




The Pattern I used: (S+) is placed in a capturing group (...) that will capture all cases of S+ (all characters that are NOT a white space, + one or more times.



I also used the .Global so you will continue matching after the first match.



Once you have captured all your words, you can then loop through the match collection and place them into an array.





Late Binding:



Dim line As String, arrStr, i As Long
line = "This is a test"

With CreateObject("VBScript.RegExp")
.Pattern = "S+"
.Global = True
If .test(line) Then
With .Execute(line)
ReDim arrStr(.Count - 1)
For i = 0 To .Count - 1
arrStr(i) = .Item(i)
Next
End With
End If
End With




Miscellaneous Notes



I would have advised just to use Split(), but you stated that there were cases where more than one consecutive space may have been an issue. If this wasn't the case, you wouldn't need regex at all, something like:



arrStr = Split(line)


Would have split on every occurance of a space







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 18 '18 at 10:08

























answered Nov 18 '18 at 6:46









K.DᴀᴠɪsK.Dᴀᴠɪs

7,159112339




7,159112339













  • The code with Late Binding worked for me.

    – kush.impetus
    Nov 18 '18 at 7:25






  • 2





    @kush.impetus If you remove the redundant capturing group it will be cleaner: .Pattern = "S+" and arrStr(i) = .Item(i) (there won't be any need to access Submatches(0))

    – Wiktor Stribiżew
    Nov 18 '18 at 10:04













  • @WiktorStribiżew thanks for pointing that out - that was leftover as part of a different route I was taking. I corrected it :)

    – K.Dᴀᴠɪs
    Nov 18 '18 at 10:09













  • @K.Davis. Thanks. Your code has made my task lot easier.

    – kush.impetus
    Nov 19 '18 at 13:10



















  • The code with Late Binding worked for me.

    – kush.impetus
    Nov 18 '18 at 7:25






  • 2





    @kush.impetus If you remove the redundant capturing group it will be cleaner: .Pattern = "S+" and arrStr(i) = .Item(i) (there won't be any need to access Submatches(0))

    – Wiktor Stribiżew
    Nov 18 '18 at 10:04













  • @WiktorStribiżew thanks for pointing that out - that was leftover as part of a different route I was taking. I corrected it :)

    – K.Dᴀᴠɪs
    Nov 18 '18 at 10:09













  • @K.Davis. Thanks. Your code has made my task lot easier.

    – kush.impetus
    Nov 19 '18 at 13:10

















The code with Late Binding worked for me.

– kush.impetus
Nov 18 '18 at 7:25





The code with Late Binding worked for me.

– kush.impetus
Nov 18 '18 at 7:25




2




2





@kush.impetus If you remove the redundant capturing group it will be cleaner: .Pattern = "S+" and arrStr(i) = .Item(i) (there won't be any need to access Submatches(0))

– Wiktor Stribiżew
Nov 18 '18 at 10:04







@kush.impetus If you remove the redundant capturing group it will be cleaner: .Pattern = "S+" and arrStr(i) = .Item(i) (there won't be any need to access Submatches(0))

– Wiktor Stribiżew
Nov 18 '18 at 10:04















@WiktorStribiżew thanks for pointing that out - that was leftover as part of a different route I was taking. I corrected it :)

– K.Dᴀᴠɪs
Nov 18 '18 at 10:09







@WiktorStribiżew thanks for pointing that out - that was leftover as part of a different route I was taking. I corrected it :)

– K.Dᴀᴠɪs
Nov 18 '18 at 10:09















@K.Davis. Thanks. Your code has made my task lot easier.

– kush.impetus
Nov 19 '18 at 13:10





@K.Davis. Thanks. Your code has made my task lot easier.

– kush.impetus
Nov 19 '18 at 13:10


















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53358309%2fhow-to-split-a-string-in-vba-to-array-by-split-function-delimited-by-regular-exp%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Guess what letter conforming each word

Port of Spain

Run scheduled task as local user group (not BUILTIN)