Mixing
Excel separate cell value
How do I split the text so each option goes to the appropriate option cell(A,B,C.D,E).
Please note that the option text length may vary as well and some option might be missing. For example option B might be missing, leaving the option in the cell as ACDE, how can I get it to skip the empty option and go to the next.
excel vba excel-formula
edited Nov 19 '18 at 15:11
BruceWayne
17k93056
asked Nov 19 '18 at 13:45
Andrew.O
12
add a comment |
How do I split the text so each option goes to the appropriate option cell(A,B,C.D,E).
Please note that the option text length may vary as well and some option might be missing. For example option B might be missing, leaving the option in the cell as ACDE, how can I get it to skip the empty option and go to the next.
excel vba excel-formula
edited Nov 19 '18 at 15:11
BruceWayne
17k93056
asked Nov 19 '18 at 13:45
Andrew.O
12
So you want to match the first letter in each string with the column letter? Could you edit your picture please to remove the extraneous text? Have you tried anything yourself?
– SJR
Nov 19 '18 at 13:54
You have to find some common logic that can be define to separate all options in your full string of options. For example each option is starting with A,B,C,D or E followed by a dot, or each option is starting with a character | etc, this logic can then be implemented in a formula to separate your text string.
– usmanhaq
Nov 19 '18 at 14:08
As an array formula this will split your example options by the|bar:Public Function SplitOptions(Target As Range) As Variant: SplitOptions = Split(Target, "|"): End Function
– Darren Bartrup-Cook
Nov 19 '18 at 14:20
1
You might consider forcing the Text Import feature.
– Dominique
Nov 19 '18 at 14:31
add a comment |
How do I split the text so each option goes to the appropriate option cell(A,B,C.D,E).
Please note that the option text length may vary as well and some option might be missing. For example option B might be missing, leaving the option in the cell as ACDE, how can I get it to skip the empty option and go to the next.
excel vba excel-formula
edited Nov 19 '18 at 15:11
BruceWayne
17k93056
asked Nov 19 '18 at 13:45
Andrew.O
12
How do I split the text so each option goes to the appropriate option cell(A,B,C.D,E).
Please note that the option text length may vary as well and some option might be missing. For example option B might be missing, leaving the option in the cell as ACDE, how can I get it to skip the empty option and go to the next.
excel vba excel-formula
excel vba excel-formula
edited Nov 19 '18 at 15:11
BruceWayne
17k93056
asked Nov 19 '18 at 13:45
Andrew.O
12
edited Nov 19 '18 at 15:11
BruceWayne
17k93056
asked Nov 19 '18 at 13:45
Andrew.O
12
edited Nov 19 '18 at 15:11
BruceWayne
17k93056
edited Nov 19 '18 at 15:11
BruceWayne
17k93056
edited Nov 19 '18 at 15:11
BruceWayne
17k93056
17k93056
asked Nov 19 '18 at 13:45
Andrew.O
12
asked Nov 19 '18 at 13:45
Andrew.O
12
asked Nov 19 '18 at 13:45
Andrew.O
12
12
So you want to match the first letter in each string with the column letter? Could you edit your picture please to remove the extraneous text? Have you tried anything yourself?
– SJR
Nov 19 '18 at 13:54
You have to find some common logic that can be define to separate all options in your full string of options. For example each option is starting with A,B,C,D or E followed by a dot, or each option is starting with a character | etc, this logic can then be implemented in a formula to separate your text string.
– usmanhaq
Nov 19 '18 at 14:08
As an array formula this will split your example options by the|bar:Public Function SplitOptions(Target As Range) As Variant: SplitOptions = Split(Target, "|"): End Function
– Darren Bartrup-Cook
Nov 19 '18 at 14:20
1
You might consider forcing the Text Import feature.
– Dominique
Nov 19 '18 at 14:31
add a comment |
So you want to match the first letter in each string with the column letter? Could you edit your picture please to remove the extraneous text? Have you tried anything yourself?
– SJR
Nov 19 '18 at 13:54
You have to find some common logic that can be define to separate all options in your full string of options. For example each option is starting with A,B,C,D or E followed by a dot, or each option is starting with a character | etc, this logic can then be implemented in a formula to separate your text string.
– usmanhaq
Nov 19 '18 at 14:08
As an array formula this will split your example options by the|bar:Public Function SplitOptions(Target As Range) As Variant: SplitOptions = Split(Target, "|"): End Function
– Darren Bartrup-Cook
Nov 19 '18 at 14:20
1
You might consider forcing the Text Import feature.
– Dominique
Nov 19 '18 at 14:31
So you want to match the first letter in each string with the column letter? Could you edit your picture please to remove the extraneous text? Have you tried anything yourself?
– SJR
Nov 19 '18 at 13:54
So you want to match the first letter in each string with the column letter? Could you edit your picture please to remove the extraneous text? Have you tried anything yourself?
– SJR
Nov 19 '18 at 13:54
You have to find some common logic that can be define to separate all options in your full string of options. For example each option is starting with A,B,C,D or E followed by a dot, or each option is starting with a character | etc, this logic can then be implemented in a formula to separate your text string.
– usmanhaq
Nov 19 '18 at 14:08
You have to find some common logic that can be define to separate all options in your full string of options. For example each option is starting with A,B,C,D or E followed by a dot, or each option is starting with a character | etc, this logic can then be implemented in a formula to separate your text string.
– usmanhaq
Nov 19 '18 at 14:08
As an array formula this will split your example options by the
| bar: Public Function SplitOptions(Target As Range) As Variant: SplitOptions = Split(Target, "|"): End Function– Darren Bartrup-Cook
Nov 19 '18 at 14:20
As an array formula this will split your example options by the
| bar: Public Function SplitOptions(Target As Range) As Variant: SplitOptions = Split(Target, "|"): End Function– Darren Bartrup-Cook
Nov 19 '18 at 14:20
1
1
You might consider forcing the Text Import feature.
– Dominique
Nov 19 '18 at 14:31
You might consider forcing the Text Import feature.
– Dominique
Nov 19 '18 at 14:31
add a comment |
1 Answer
1
active
oldest
votes
I would tend to use 2 cells working together.
Typically, assuming that the string to parse is in C5, your cell first cell (D5 in my example) could contain:
=MID(C5,FIND("|",C5)+1,LEN(C5))
This cell would contain the remaining string to parse.
Your second cell could contain:
=LEFT(C5,LEN(C5)-LEN(D5)-1)
It would use the remaining string to parse as determined above and take the left part of it until the next delimiter.
If you do so, the last column will not work, so you will have to take care about manually.
answered Nov 19 '18 at 15:22
FloT
20919
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%2f53375985%2fexcel-separate-cell-value%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
I would tend to use 2 cells working together.
Typically, assuming that the string to parse is in C5, your cell first cell (D5 in my example) could contain:
=MID(C5,FIND("|",C5)+1,LEN(C5))
This cell would contain the remaining string to parse.
Your second cell could contain:
=LEFT(C5,LEN(C5)-LEN(D5)-1)
It would use the remaining string to parse as determined above and take the left part of it until the next delimiter.
If you do so, the last column will not work, so you will have to take care about manually.
answered Nov 19 '18 at 15:22
FloT
20919
add a comment |
I would tend to use 2 cells working together.
Typically, assuming that the string to parse is in C5, your cell first cell (D5 in my example) could contain:
=MID(C5,FIND("|",C5)+1,LEN(C5))
This cell would contain the remaining string to parse.
Your second cell could contain:
=LEFT(C5,LEN(C5)-LEN(D5)-1)
It would use the remaining string to parse as determined above and take the left part of it until the next delimiter.
If you do so, the last column will not work, so you will have to take care about manually.
answered Nov 19 '18 at 15:22
FloT
20919
add a comment |
I would tend to use 2 cells working together.
Typically, assuming that the string to parse is in C5, your cell first cell (D5 in my example) could contain:
=MID(C5,FIND("|",C5)+1,LEN(C5))
This cell would contain the remaining string to parse.
Your second cell could contain:
=LEFT(C5,LEN(C5)-LEN(D5)-1)
It would use the remaining string to parse as determined above and take the left part of it until the next delimiter.
If you do so, the last column will not work, so you will have to take care about manually.
answered Nov 19 '18 at 15:22
FloT
20919
I would tend to use 2 cells working together.
Typically, assuming that the string to parse is in C5, your cell first cell (D5 in my example) could contain:
=MID(C5,FIND("|",C5)+1,LEN(C5))
This cell would contain the remaining string to parse.
Your second cell could contain:
=LEFT(C5,LEN(C5)-LEN(D5)-1)
It would use the remaining string to parse as determined above and take the left part of it until the next delimiter.
If you do so, the last column will not work, so you will have to take care about manually.
answered Nov 19 '18 at 15:22
FloT
20919
answered Nov 19 '18 at 15:22
FloT
20919
answered Nov 19 '18 at 15:22
FloT
20919
answered Nov 19 '18 at 15:22
FloT
20919
20919
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53375985%2fexcel-separate-cell-value%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
So you want to match the first letter in each string with the column letter? Could you edit your picture please to remove the extraneous text? Have you tried anything yourself?
– SJR
Nov 19 '18 at 13:54
You have to find some common logic that can be define to separate all options in your full string of options. For example each option is starting with A,B,C,D or E followed by a dot, or each option is starting with a character | etc, this logic can then be implemented in a formula to separate your text string.
– usmanhaq
Nov 19 '18 at 14:08
As an array formula this will split your example options by the
|bar:Public Function SplitOptions(Target As Range) As Variant: SplitOptions = Split(Target, "|"): End Function– Darren Bartrup-Cook
Nov 19 '18 at 14:20
1
You might consider forcing the Text Import feature.
– Dominique
Nov 19 '18 at 14:31