excel--various substrings in a cell and return different values
I have a cell that has some or all of the following letters--T,A,B,C,F. If it is an individual letter, I would want .105 returned for T or F; .263 returned if either A, B or C. If multiple letters are found, I would want the sum of the letters found to be returned.--the example would be T,B in a cell should return .368 while A, B, C would return .79
I would like the value to be put in a single cell
excel
asked Nov 12 '18 at 22:34
Jayber
1
add a comment |
I have a cell that has some or all of the following letters--T,A,B,C,F. If it is an individual letter, I would want .105 returned for T or F; .263 returned if either A, B or C. If multiple letters are found, I would want the sum of the letters found to be returned.--the example would be T,B in a cell should return .368 while A, B, C would return .79
I would like the value to be put in a single cell
excel
asked Nov 12 '18 at 22:34
Jayber
1
1
Hello, can you share what you have tried?
– urdearboy
Nov 12 '18 at 22:35
1
What approaches have you tried so far?
– Michal Rosa
Nov 12 '18 at 23:07
Thanks to all who provided the ideas. I have never worked with an array and I really didn't understand some of the commands/logic below. Am sure they would work--I went ahead and used the non-array approach. Best regards--
– Jayber
Nov 13 '18 at 21:19
add a comment |
I have a cell that has some or all of the following letters--T,A,B,C,F. If it is an individual letter, I would want .105 returned for T or F; .263 returned if either A, B or C. If multiple letters are found, I would want the sum of the letters found to be returned.--the example would be T,B in a cell should return .368 while A, B, C would return .79
I would like the value to be put in a single cell
excel
asked Nov 12 '18 at 22:34
Jayber
1
I have a cell that has some or all of the following letters--T,A,B,C,F. If it is an individual letter, I would want .105 returned for T or F; .263 returned if either A, B or C. If multiple letters are found, I would want the sum of the letters found to be returned.--the example would be T,B in a cell should return .368 while A, B, C would return .79
I would like the value to be put in a single cell
excel
excel
asked Nov 12 '18 at 22:34
Jayber
1
asked Nov 12 '18 at 22:34
Jayber
1
asked Nov 12 '18 at 22:34
Jayber
1
asked Nov 12 '18 at 22:34
Jayber
1
asked Nov 12 '18 at 22:34
Jayber
1
1
1
Hello, can you share what you have tried?
– urdearboy
Nov 12 '18 at 22:35
1
What approaches have you tried so far?
– Michal Rosa
Nov 12 '18 at 23:07
Thanks to all who provided the ideas. I have never worked with an array and I really didn't understand some of the commands/logic below. Am sure they would work--I went ahead and used the non-array approach. Best regards--
– Jayber
Nov 13 '18 at 21:19
add a comment |
1
Hello, can you share what you have tried?
– urdearboy
Nov 12 '18 at 22:35
1
What approaches have you tried so far?
– Michal Rosa
Nov 12 '18 at 23:07
Thanks to all who provided the ideas. I have never worked with an array and I really didn't understand some of the commands/logic below. Am sure they would work--I went ahead and used the non-array approach. Best regards--
– Jayber
Nov 13 '18 at 21:19
1
1
Hello, can you share what you have tried?
– urdearboy
Nov 12 '18 at 22:35
Hello, can you share what you have tried?
– urdearboy
Nov 12 '18 at 22:35
1
1
What approaches have you tried so far?
– Michal Rosa
Nov 12 '18 at 23:07
What approaches have you tried so far?
– Michal Rosa
Nov 12 '18 at 23:07
Thanks to all who provided the ideas. I have never worked with an array and I really didn't understand some of the commands/logic below. Am sure they would work--I went ahead and used the non-array approach. Best regards--
– Jayber
Nov 13 '18 at 21:19
Thanks to all who provided the ideas. I have never worked with an array and I really didn't understand some of the commands/logic below. Am sure they would work--I went ahead and used the non-array approach. Best regards--
– Jayber
Nov 13 '18 at 21:19
add a comment |
3 Answers
3
active
oldest
votes
It's an array formula so have to be entered while holding Ctrl-Shift =SUMPRODUCT(((Table1[Letter])=TRANSPOSE(MID(D2,ROW(INDIRECT("1:"&LEN(D2))),1)))*Table1[Value])
answered Nov 12 '18 at 23:19
Michal Rosa
1,3161814
add a comment |
Another variant handling , in your criteria cell :
{=SUM(IF(A2:A6=TRANSPOSE(INDEX(MID(SUBSTITUTE(F4,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(F4,",","")))),1),)),B2:B6,0))}
answered Nov 13 '18 at 0:00
Display name
55416
add a comment |
hate to use array so here is a non-array formula
=IF(ISERROR(MATCH("*T*",$A$2,0)),0,0.105)+IF(ISERROR(MATCH("*F*",$A$2,0)),0,0.105)+IF(ISERROR(MATCH("*A*",$A$2,0)),0,0.263)+IF(ISERROR(MATCH("*B*",$A$2,0)),0,0.263)+IF(ISERROR(MATCH("*C*",$A$2,0)),0,0.263)
answered Nov 13 '18 at 1:39
Osman Wong
1037
NestedIFs are never the answer, even if the question asks for nestedIFs. It's a horrible practice that makes for unreadable formulas.
– Michal Rosa
Nov 17 '18 at 2:04
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%2f53271078%2fexcel-various-substrings-in-a-cell-and-return-different-values%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
It's an array formula so have to be entered while holding Ctrl-Shift =SUMPRODUCT(((Table1[Letter])=TRANSPOSE(MID(D2,ROW(INDIRECT("1:"&LEN(D2))),1)))*Table1[Value])
answered Nov 12 '18 at 23:19
Michal Rosa
1,3161814
add a comment |
It's an array formula so have to be entered while holding Ctrl-Shift =SUMPRODUCT(((Table1[Letter])=TRANSPOSE(MID(D2,ROW(INDIRECT("1:"&LEN(D2))),1)))*Table1[Value])
answered Nov 12 '18 at 23:19
Michal Rosa
1,3161814
add a comment |
It's an array formula so have to be entered while holding Ctrl-Shift =SUMPRODUCT(((Table1[Letter])=TRANSPOSE(MID(D2,ROW(INDIRECT("1:"&LEN(D2))),1)))*Table1[Value])
answered Nov 12 '18 at 23:19
Michal Rosa
1,3161814
It's an array formula so have to be entered while holding Ctrl-Shift =SUMPRODUCT(((Table1[Letter])=TRANSPOSE(MID(D2,ROW(INDIRECT("1:"&LEN(D2))),1)))*Table1[Value])
answered Nov 12 '18 at 23:19
Michal Rosa
1,3161814
answered Nov 12 '18 at 23:19
Michal Rosa
1,3161814
answered Nov 12 '18 at 23:19
Michal Rosa
1,3161814
answered Nov 12 '18 at 23:19
Michal Rosa
1,3161814
1,3161814
add a comment |
add a comment |
Another variant handling , in your criteria cell :
{=SUM(IF(A2:A6=TRANSPOSE(INDEX(MID(SUBSTITUTE(F4,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(F4,",","")))),1),)),B2:B6,0))}
answered Nov 13 '18 at 0:00
Display name
55416
add a comment |
Another variant handling , in your criteria cell :
{=SUM(IF(A2:A6=TRANSPOSE(INDEX(MID(SUBSTITUTE(F4,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(F4,",","")))),1),)),B2:B6,0))}
answered Nov 13 '18 at 0:00
Display name
55416
add a comment |
Another variant handling , in your criteria cell :
{=SUM(IF(A2:A6=TRANSPOSE(INDEX(MID(SUBSTITUTE(F4,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(F4,",","")))),1),)),B2:B6,0))}
answered Nov 13 '18 at 0:00
Display name
55416
Another variant handling , in your criteria cell :
{=SUM(IF(A2:A6=TRANSPOSE(INDEX(MID(SUBSTITUTE(F4,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(F4,",","")))),1),)),B2:B6,0))}
answered Nov 13 '18 at 0:00
Display name
55416
answered Nov 13 '18 at 0:00
Display name
55416
answered Nov 13 '18 at 0:00
Display name
55416
answered Nov 13 '18 at 0:00
Display name
55416
55416
add a comment |
add a comment |
hate to use array so here is a non-array formula
=IF(ISERROR(MATCH("*T*",$A$2,0)),0,0.105)+IF(ISERROR(MATCH("*F*",$A$2,0)),0,0.105)+IF(ISERROR(MATCH("*A*",$A$2,0)),0,0.263)+IF(ISERROR(MATCH("*B*",$A$2,0)),0,0.263)+IF(ISERROR(MATCH("*C*",$A$2,0)),0,0.263)
answered Nov 13 '18 at 1:39
Osman Wong
1037
NestedIFs are never the answer, even if the question asks for nestedIFs. It's a horrible practice that makes for unreadable formulas.
– Michal Rosa
Nov 17 '18 at 2:04
add a comment |
hate to use array so here is a non-array formula
=IF(ISERROR(MATCH("*T*",$A$2,0)),0,0.105)+IF(ISERROR(MATCH("*F*",$A$2,0)),0,0.105)+IF(ISERROR(MATCH("*A*",$A$2,0)),0,0.263)+IF(ISERROR(MATCH("*B*",$A$2,0)),0,0.263)+IF(ISERROR(MATCH("*C*",$A$2,0)),0,0.263)
answered Nov 13 '18 at 1:39
Osman Wong
1037
NestedIFs are never the answer, even if the question asks for nestedIFs. It's a horrible practice that makes for unreadable formulas.
– Michal Rosa
Nov 17 '18 at 2:04
add a comment |
hate to use array so here is a non-array formula
=IF(ISERROR(MATCH("*T*",$A$2,0)),0,0.105)+IF(ISERROR(MATCH("*F*",$A$2,0)),0,0.105)+IF(ISERROR(MATCH("*A*",$A$2,0)),0,0.263)+IF(ISERROR(MATCH("*B*",$A$2,0)),0,0.263)+IF(ISERROR(MATCH("*C*",$A$2,0)),0,0.263)
answered Nov 13 '18 at 1:39
Osman Wong
1037
hate to use array so here is a non-array formula
=IF(ISERROR(MATCH("*T*",$A$2,0)),0,0.105)+IF(ISERROR(MATCH("*F*",$A$2,0)),0,0.105)+IF(ISERROR(MATCH("*A*",$A$2,0)),0,0.263)+IF(ISERROR(MATCH("*B*",$A$2,0)),0,0.263)+IF(ISERROR(MATCH("*C*",$A$2,0)),0,0.263)
answered Nov 13 '18 at 1:39
Osman Wong
1037
answered Nov 13 '18 at 1:39
Osman Wong
1037
answered Nov 13 '18 at 1:39
Osman Wong
1037
answered Nov 13 '18 at 1:39
Osman Wong
1037
1037
NestedIFs are never the answer, even if the question asks for nestedIFs. It's a horrible practice that makes for unreadable formulas.
– Michal Rosa
Nov 17 '18 at 2:04
add a comment |
NestedIFs are never the answer, even if the question asks for nestedIFs. It's a horrible practice that makes for unreadable formulas.
– Michal Rosa
Nov 17 '18 at 2:04
Nested
IFs are never the answer, even if the question asks for nested IFs. It's a horrible practice that makes for unreadable formulas.– Michal Rosa
Nov 17 '18 at 2:04
Nested
IFs are never the answer, even if the question asks for nested IFs. It's a horrible practice that makes for unreadable formulas.– Michal Rosa
Nov 17 '18 at 2:04
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%2f53271078%2fexcel-various-substrings-in-a-cell-and-return-different-values%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
1
Hello, can you share what you have tried?
– urdearboy
Nov 12 '18 at 22:35
1
What approaches have you tried so far?
– Michal Rosa
Nov 12 '18 at 23:07
Thanks to all who provided the ideas. I have never worked with an array and I really didn't understand some of the commands/logic below. Am sure they would work--I went ahead and used the non-array approach. Best regards--
– Jayber
Nov 13 '18 at 21:19