Mixing
How do I split characters from a string of varying length in a cell?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I am attempting to split characters from the following string:
135/GB 102 Main (DA)/Shelf 1/Rack 1/RckShlf 2/Bx 2/Pos35
Sub SplitString()
Dim c As Range
For Each c In Range("N3:N6")
Debug.Print Split(c, "/")(6)
Debug.Print Split(c, "/")(5)
Debug.Print Split(c, "/")(4)
Debug.Print Split(c, "/")(3)
Debug.Print Split(c, "/")(2)
Debug.Print Split(c, "/")(1)
Next c
End Sub
Ideally I would like to isolate only the numerical characters between each "/" but it would be a huge time saver just to be able to split all of the characters between "/".
excel vba
edited Jan 3 at 16:16
Martin
5,4751332
asked Jan 3 at 15:57
Thomas BeverThomas Bever
12
add a comment |
I am attempting to split characters from the following string:
135/GB 102 Main (DA)/Shelf 1/Rack 1/RckShlf 2/Bx 2/Pos35
Sub SplitString()
Dim c As Range
For Each c In Range("N3:N6")
Debug.Print Split(c, "/")(6)
Debug.Print Split(c, "/")(5)
Debug.Print Split(c, "/")(4)
Debug.Print Split(c, "/")(3)
Debug.Print Split(c, "/")(2)
Debug.Print Split(c, "/")(1)
Next c
End Sub
Ideally I would like to isolate only the numerical characters between each "/" but it would be a huge time saver just to be able to split all of the characters between "/".
excel vba
edited Jan 3 at 16:16
Martin
5,4751332
asked Jan 3 at 15:57
Thomas BeverThomas Bever
12
Have you triedText to Columns?
– cybernetic.nomad
Jan 3 at 15:58
or in VBA there's the SPLIT function
– Harassed Dad
Jan 3 at 15:59
add a comment |
I am attempting to split characters from the following string:
135/GB 102 Main (DA)/Shelf 1/Rack 1/RckShlf 2/Bx 2/Pos35
Sub SplitString()
Dim c As Range
For Each c In Range("N3:N6")
Debug.Print Split(c, "/")(6)
Debug.Print Split(c, "/")(5)
Debug.Print Split(c, "/")(4)
Debug.Print Split(c, "/")(3)
Debug.Print Split(c, "/")(2)
Debug.Print Split(c, "/")(1)
Next c
End Sub
Ideally I would like to isolate only the numerical characters between each "/" but it would be a huge time saver just to be able to split all of the characters between "/".
excel vba
edited Jan 3 at 16:16
Martin
5,4751332
asked Jan 3 at 15:57
Thomas BeverThomas Bever
12
I am attempting to split characters from the following string:
135/GB 102 Main (DA)/Shelf 1/Rack 1/RckShlf 2/Bx 2/Pos35
Sub SplitString()
Dim c As Range
For Each c In Range("N3:N6")
Debug.Print Split(c, "/")(6)
Debug.Print Split(c, "/")(5)
Debug.Print Split(c, "/")(4)
Debug.Print Split(c, "/")(3)
Debug.Print Split(c, "/")(2)
Debug.Print Split(c, "/")(1)
Next c
End Sub
Ideally I would like to isolate only the numerical characters between each "/" but it would be a huge time saver just to be able to split all of the characters between "/".
excel vba
excel vba
edited Jan 3 at 16:16
Martin
5,4751332
asked Jan 3 at 15:57
Thomas BeverThomas Bever
12
edited Jan 3 at 16:16
Martin
5,4751332
asked Jan 3 at 15:57
Thomas BeverThomas Bever
12
edited Jan 3 at 16:16
Martin
5,4751332
edited Jan 3 at 16:16
Martin
5,4751332
edited Jan 3 at 16:16
Martin
5,4751332
5,4751332
asked Jan 3 at 15:57
Thomas BeverThomas Bever
12
asked Jan 3 at 15:57
Thomas BeverThomas Bever
12
asked Jan 3 at 15:57
Thomas BeverThomas Bever
12
12
Have you triedText to Columns?
– cybernetic.nomad
Jan 3 at 15:58
or in VBA there's the SPLIT function
– Harassed Dad
Jan 3 at 15:59
add a comment |
Have you triedText to Columns?
– cybernetic.nomad
Jan 3 at 15:58
or in VBA there's the SPLIT function
– Harassed Dad
Jan 3 at 15:59
Have you tried
Text to Columns?– cybernetic.nomad
Jan 3 at 15:58
Have you tried
Text to Columns?– cybernetic.nomad
Jan 3 at 15:58
or in VBA there's the SPLIT function
– Harassed Dad
Jan 3 at 15:59
or in VBA there's the SPLIT function
– Harassed Dad
Jan 3 at 15:59
add a comment |
2 Answers
2
active
oldest
votes
I think you'd be better off going through the whole string rather than breaking it into sections and then trying to find the numbers in each section.
Consider the following code:
Sub SplitString()
Dim c As Range
Dim Counter As Integer
For Each c In Range("N3:N6")
Dim Section As String
For Counter = 1 To Len(c)
Dim s As String
s = Mid(c, Counter, 1)
If IsNumeric(s) Then
Section = Section + s
ElseIf (s = "/") Then
Debug.Print Section
Section = ""
End If
Next
Debug.Print Section
Section = ""
Next c
End Sub
This will go through your range, and create a string containing all of the numbers in each section, which is will then Debug.Print when the section ends (when it finds a /).
The output on the above will be:
135
102
1
1
2
2
35
If you need to go through the string in reverse order then change the counter to run from Len(c) to 1.
answered Jan 3 at 16:15
MartinMartin
5,4751332
add a comment |
You can extract numbers with regular expression. Since you didn't mention, in which way you need to get the result, I assume this is an array:
Function GetNums(s)
Dim arr(), m, mc, x
With CreateObject("VBScript.RegExp")
.Global = True: .Pattern = "d+"
Set mc = .Execute(s)
If mc.Count > 0 Then
For Each m In mc
x = x + 1: ReDim Preserve arr(1 To x)
arr(x) = m.Value
Next
End If
End With
GetNums = arr
End Function
Sub test()
Dim s, z, x
s = "135/GB 102 Main (DA)/Shelf 1/Rack 1/RckShlf 2/Bx 2/Pos35"
z = GetNums(s)
For x = 1 To UBound(z): Debug.Print z(x): Next
End Sub
' Output:
' 102
' 1
' 1
' 2
' 2
' 35
answered Jan 3 at 20:58
JohnyLJohnyL
3,73811025
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54025737%2fhow-do-i-split-characters-from-a-string-of-varying-length-in-a-cell%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I think you'd be better off going through the whole string rather than breaking it into sections and then trying to find the numbers in each section.
Consider the following code:
Sub SplitString()
Dim c As Range
Dim Counter As Integer
For Each c In Range("N3:N6")
Dim Section As String
For Counter = 1 To Len(c)
Dim s As String
s = Mid(c, Counter, 1)
If IsNumeric(s) Then
Section = Section + s
ElseIf (s = "/") Then
Debug.Print Section
Section = ""
End If
Next
Debug.Print Section
Section = ""
Next c
End Sub
This will go through your range, and create a string containing all of the numbers in each section, which is will then Debug.Print when the section ends (when it finds a /).
The output on the above will be:
135
102
1
1
2
2
35
If you need to go through the string in reverse order then change the counter to run from Len(c) to 1.
answered Jan 3 at 16:15
MartinMartin
5,4751332
add a comment |
I think you'd be better off going through the whole string rather than breaking it into sections and then trying to find the numbers in each section.
Consider the following code:
Sub SplitString()
Dim c As Range
Dim Counter As Integer
For Each c In Range("N3:N6")
Dim Section As String
For Counter = 1 To Len(c)
Dim s As String
s = Mid(c, Counter, 1)
If IsNumeric(s) Then
Section = Section + s
ElseIf (s = "/") Then
Debug.Print Section
Section = ""
End If
Next
Debug.Print Section
Section = ""
Next c
End Sub
This will go through your range, and create a string containing all of the numbers in each section, which is will then Debug.Print when the section ends (when it finds a /).
The output on the above will be:
135
102
1
1
2
2
35
If you need to go through the string in reverse order then change the counter to run from Len(c) to 1.
answered Jan 3 at 16:15
MartinMartin
5,4751332
add a comment |
I think you'd be better off going through the whole string rather than breaking it into sections and then trying to find the numbers in each section.
Consider the following code:
Sub SplitString()
Dim c As Range
Dim Counter As Integer
For Each c In Range("N3:N6")
Dim Section As String
For Counter = 1 To Len(c)
Dim s As String
s = Mid(c, Counter, 1)
If IsNumeric(s) Then
Section = Section + s
ElseIf (s = "/") Then
Debug.Print Section
Section = ""
End If
Next
Debug.Print Section
Section = ""
Next c
End Sub
This will go through your range, and create a string containing all of the numbers in each section, which is will then Debug.Print when the section ends (when it finds a /).
The output on the above will be:
135
102
1
1
2
2
35
If you need to go through the string in reverse order then change the counter to run from Len(c) to 1.
answered Jan 3 at 16:15
MartinMartin
5,4751332
I think you'd be better off going through the whole string rather than breaking it into sections and then trying to find the numbers in each section.
Consider the following code:
Sub SplitString()
Dim c As Range
Dim Counter As Integer
For Each c In Range("N3:N6")
Dim Section As String
For Counter = 1 To Len(c)
Dim s As String
s = Mid(c, Counter, 1)
If IsNumeric(s) Then
Section = Section + s
ElseIf (s = "/") Then
Debug.Print Section
Section = ""
End If
Next
Debug.Print Section
Section = ""
Next c
End Sub
This will go through your range, and create a string containing all of the numbers in each section, which is will then Debug.Print when the section ends (when it finds a /).
The output on the above will be:
135
102
1
1
2
2
35
If you need to go through the string in reverse order then change the counter to run from Len(c) to 1.
answered Jan 3 at 16:15
MartinMartin
5,4751332
answered Jan 3 at 16:15
MartinMartin
5,4751332
answered Jan 3 at 16:15
MartinMartin
5,4751332
answered Jan 3 at 16:15
MartinMartin
5,4751332
5,4751332
add a comment |
add a comment |
You can extract numbers with regular expression. Since you didn't mention, in which way you need to get the result, I assume this is an array:
Function GetNums(s)
Dim arr(), m, mc, x
With CreateObject("VBScript.RegExp")
.Global = True: .Pattern = "d+"
Set mc = .Execute(s)
If mc.Count > 0 Then
For Each m In mc
x = x + 1: ReDim Preserve arr(1 To x)
arr(x) = m.Value
Next
End If
End With
GetNums = arr
End Function
Sub test()
Dim s, z, x
s = "135/GB 102 Main (DA)/Shelf 1/Rack 1/RckShlf 2/Bx 2/Pos35"
z = GetNums(s)
For x = 1 To UBound(z): Debug.Print z(x): Next
End Sub
' Output:
' 102
' 1
' 1
' 2
' 2
' 35
answered Jan 3 at 20:58
JohnyLJohnyL
3,73811025
add a comment |
You can extract numbers with regular expression. Since you didn't mention, in which way you need to get the result, I assume this is an array:
Function GetNums(s)
Dim arr(), m, mc, x
With CreateObject("VBScript.RegExp")
.Global = True: .Pattern = "d+"
Set mc = .Execute(s)
If mc.Count > 0 Then
For Each m In mc
x = x + 1: ReDim Preserve arr(1 To x)
arr(x) = m.Value
Next
End If
End With
GetNums = arr
End Function
Sub test()
Dim s, z, x
s = "135/GB 102 Main (DA)/Shelf 1/Rack 1/RckShlf 2/Bx 2/Pos35"
z = GetNums(s)
For x = 1 To UBound(z): Debug.Print z(x): Next
End Sub
' Output:
' 102
' 1
' 1
' 2
' 2
' 35
answered Jan 3 at 20:58
JohnyLJohnyL
3,73811025
add a comment |
You can extract numbers with regular expression. Since you didn't mention, in which way you need to get the result, I assume this is an array:
Function GetNums(s)
Dim arr(), m, mc, x
With CreateObject("VBScript.RegExp")
.Global = True: .Pattern = "d+"
Set mc = .Execute(s)
If mc.Count > 0 Then
For Each m In mc
x = x + 1: ReDim Preserve arr(1 To x)
arr(x) = m.Value
Next
End If
End With
GetNums = arr
End Function
Sub test()
Dim s, z, x
s = "135/GB 102 Main (DA)/Shelf 1/Rack 1/RckShlf 2/Bx 2/Pos35"
z = GetNums(s)
For x = 1 To UBound(z): Debug.Print z(x): Next
End Sub
' Output:
' 102
' 1
' 1
' 2
' 2
' 35
answered Jan 3 at 20:58
JohnyLJohnyL
3,73811025
You can extract numbers with regular expression. Since you didn't mention, in which way you need to get the result, I assume this is an array:
Function GetNums(s)
Dim arr(), m, mc, x
With CreateObject("VBScript.RegExp")
.Global = True: .Pattern = "d+"
Set mc = .Execute(s)
If mc.Count > 0 Then
For Each m In mc
x = x + 1: ReDim Preserve arr(1 To x)
arr(x) = m.Value
Next
End If
End With
GetNums = arr
End Function
Sub test()
Dim s, z, x
s = "135/GB 102 Main (DA)/Shelf 1/Rack 1/RckShlf 2/Bx 2/Pos35"
z = GetNums(s)
For x = 1 To UBound(z): Debug.Print z(x): Next
End Sub
' Output:
' 102
' 1
' 1
' 2
' 2
' 35
answered Jan 3 at 20:58
JohnyLJohnyL
3,73811025
answered Jan 3 at 20:58
JohnyLJohnyL
3,73811025
answered Jan 3 at 20:58
JohnyLJohnyL
3,73811025
answered Jan 3 at 20:58
JohnyLJohnyL
3,73811025
3,73811025
add a comment |
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54025737%2fhow-do-i-split-characters-from-a-string-of-varying-length-in-a-cell%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
Have you tried
Text to Columns?– cybernetic.nomad
Jan 3 at 15:58
or in VBA there's the SPLIT function
– Harassed Dad
Jan 3 at 15:59