How do I extract text between second quotation and first comma?
0
The text string is
"Xie,Jianhui" "3246 S. 112 st., Milwaukee, WI 53202" M231
I need to extract 3246 S. 112 st. into its own cell.
I can't figure out the correct function combo to use in Excel. Help?
worksheet-function
edited Feb 7 at 16:28
JakeGould
32.1k1098141
asked Feb 7 at 16:14
Struggling1Struggling1
31
add a comment |
0
The text string is
"Xie,Jianhui" "3246 S. 112 st., Milwaukee, WI 53202" M231
I need to extract 3246 S. 112 st. into its own cell.
I can't figure out the correct function combo to use in Excel. Help?
worksheet-function
edited Feb 7 at 16:28
JakeGould
32.1k1098141
asked Feb 7 at 16:14
Struggling1Struggling1
31
1
I see three quotes before and it is the second comma in the string. Are they always that way?
– Scott Craner
Feb 7 at 16:17
Yes, all entries in the sheet are exactly the same.
– Struggling1
Feb 7 at 16:19
If they format and string length is the same, you can use the left() function. You could also use mid() or find(). P.S. I hope that's not a real person's name and address...
– spikey_richie
Feb 7 at 16:20
Also, see superuser.com/questions/1043046/…
– spikey_richie
Feb 7 at 16:22
Please Edit the question, adding tags for microsoft-excel and the specific version you're working with. The answers available may change depending on version.
– Christopher Hostage
Feb 7 at 16:32
add a comment |
0
0
0
The text string is
"Xie,Jianhui" "3246 S. 112 st., Milwaukee, WI 53202" M231
I need to extract 3246 S. 112 st. into its own cell.
I can't figure out the correct function combo to use in Excel. Help?
worksheet-function
edited Feb 7 at 16:28
JakeGould
32.1k1098141
asked Feb 7 at 16:14
Struggling1Struggling1
31
The text string is
"Xie,Jianhui" "3246 S. 112 st., Milwaukee, WI 53202" M231
I need to extract 3246 S. 112 st. into its own cell.
I can't figure out the correct function combo to use in Excel. Help?
worksheet-function
worksheet-function
edited Feb 7 at 16:28
JakeGould
32.1k1098141
asked Feb 7 at 16:14
Struggling1Struggling1
31
edited Feb 7 at 16:28
JakeGould
32.1k1098141
asked Feb 7 at 16:14
Struggling1Struggling1
31
edited Feb 7 at 16:28
JakeGould
32.1k1098141
edited Feb 7 at 16:28
JakeGould
32.1k1098141
edited Feb 7 at 16:28
JakeGould
32.1k1098141
32.1k1098141
asked Feb 7 at 16:14
Struggling1Struggling1
31
asked Feb 7 at 16:14
Struggling1Struggling1
31
asked Feb 7 at 16:14
Struggling1Struggling1
31
31
1
I see three quotes before and it is the second comma in the string. Are they always that way?
– Scott Craner
Feb 7 at 16:17
Yes, all entries in the sheet are exactly the same.
– Struggling1
Feb 7 at 16:19
If they format and string length is the same, you can use the left() function. You could also use mid() or find(). P.S. I hope that's not a real person's name and address...
– spikey_richie
Feb 7 at 16:20
Also, see superuser.com/questions/1043046/…
– spikey_richie
Feb 7 at 16:22
Please Edit the question, adding tags for microsoft-excel and the specific version you're working with. The answers available may change depending on version.
– Christopher Hostage
Feb 7 at 16:32
add a comment |
1
I see three quotes before and it is the second comma in the string. Are they always that way?
– Scott Craner
Feb 7 at 16:17
Yes, all entries in the sheet are exactly the same.
– Struggling1
Feb 7 at 16:19
If they format and string length is the same, you can use the left() function. You could also use mid() or find(). P.S. I hope that's not a real person's name and address...
– spikey_richie
Feb 7 at 16:20
Also, see superuser.com/questions/1043046/…
– spikey_richie
Feb 7 at 16:22
Please Edit the question, adding tags for microsoft-excel and the specific version you're working with. The answers available may change depending on version.
– Christopher Hostage
Feb 7 at 16:32
1
1
I see three quotes before and it is the second comma in the string. Are they always that way?
– Scott Craner
Feb 7 at 16:17
I see three quotes before and it is the second comma in the string. Are they always that way?
– Scott Craner
Feb 7 at 16:17
Yes, all entries in the sheet are exactly the same.
– Struggling1
Feb 7 at 16:19
Yes, all entries in the sheet are exactly the same.
– Struggling1
Feb 7 at 16:19
If they format and string length is the same, you can use the left() function. You could also use mid() or find(). P.S. I hope that's not a real person's name and address...
– spikey_richie
Feb 7 at 16:20
If they format and string length is the same, you can use the left() function. You could also use mid() or find(). P.S. I hope that's not a real person's name and address...
– spikey_richie
Feb 7 at 16:20
Also, see superuser.com/questions/1043046/…
– spikey_richie
Feb 7 at 16:22
Also, see superuser.com/questions/1043046/…
– spikey_richie
Feb 7 at 16:22
Please Edit the question, adding tags for microsoft-excel and the specific version you're working with. The answers available may change depending on version.
– Christopher Hostage
Feb 7 at 16:32
Please Edit the question, adding tags for microsoft-excel and the specific version you're working with. The answers available may change depending on version.
– Christopher Hostage
Feb 7 at 16:32
add a comment |
1 Answer
1
active
oldest
votes
1
You can use Find(Substitute()) to find the third " and then find the first , after that to use in a MID.
=MID(A1,FIND("}}}",SUBSTITUTE(A1,"""","}}}",3))+1,FIND(",",REPLACE(A1,1,FIND("}}}",SUBSTITUTE(A1,"""","}}}",3))+1,"")))
answered Feb 7 at 16:22
Scott CranerScott Craner
12.4k11218
Amazing- that worked! Thank you So Much!
– Struggling1
Feb 7 at 16:24
@Struggling1 please mark as correct by clicking the check mark by the answer.
– Scott Craner
Feb 7 at 16:35
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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
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%2fsuperuser.com%2fquestions%2f1403196%2fhow-do-i-extract-text-between-second-quotation-and-first-comma%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
1
You can use Find(Substitute()) to find the third " and then find the first , after that to use in a MID.
=MID(A1,FIND("}}}",SUBSTITUTE(A1,"""","}}}",3))+1,FIND(",",REPLACE(A1,1,FIND("}}}",SUBSTITUTE(A1,"""","}}}",3))+1,"")))
answered Feb 7 at 16:22
Scott CranerScott Craner
12.4k11218
Amazing- that worked! Thank you So Much!
– Struggling1
Feb 7 at 16:24
@Struggling1 please mark as correct by clicking the check mark by the answer.
– Scott Craner
Feb 7 at 16:35
add a comment |
1
You can use Find(Substitute()) to find the third " and then find the first , after that to use in a MID.
=MID(A1,FIND("}}}",SUBSTITUTE(A1,"""","}}}",3))+1,FIND(",",REPLACE(A1,1,FIND("}}}",SUBSTITUTE(A1,"""","}}}",3))+1,"")))
answered Feb 7 at 16:22
Scott CranerScott Craner
12.4k11218
Amazing- that worked! Thank you So Much!
– Struggling1
Feb 7 at 16:24
@Struggling1 please mark as correct by clicking the check mark by the answer.
– Scott Craner
Feb 7 at 16:35
add a comment |
1
1
1
You can use Find(Substitute()) to find the third " and then find the first , after that to use in a MID.
=MID(A1,FIND("}}}",SUBSTITUTE(A1,"""","}}}",3))+1,FIND(",",REPLACE(A1,1,FIND("}}}",SUBSTITUTE(A1,"""","}}}",3))+1,"")))
answered Feb 7 at 16:22
Scott CranerScott Craner
12.4k11218
You can use Find(Substitute()) to find the third " and then find the first , after that to use in a MID.
=MID(A1,FIND("}}}",SUBSTITUTE(A1,"""","}}}",3))+1,FIND(",",REPLACE(A1,1,FIND("}}}",SUBSTITUTE(A1,"""","}}}",3))+1,"")))
answered Feb 7 at 16:22
Scott CranerScott Craner
12.4k11218
answered Feb 7 at 16:22
Scott CranerScott Craner
12.4k11218
answered Feb 7 at 16:22
Scott CranerScott Craner
12.4k11218
answered Feb 7 at 16:22
Scott CranerScott Craner
12.4k11218
12.4k11218
Amazing- that worked! Thank you So Much!
– Struggling1
Feb 7 at 16:24
@Struggling1 please mark as correct by clicking the check mark by the answer.
– Scott Craner
Feb 7 at 16:35
add a comment |
Amazing- that worked! Thank you So Much!
– Struggling1
Feb 7 at 16:24
@Struggling1 please mark as correct by clicking the check mark by the answer.
– Scott Craner
Feb 7 at 16:35
Amazing- that worked! Thank you So Much!
– Struggling1
Feb 7 at 16:24
Amazing- that worked! Thank you So Much!
– Struggling1
Feb 7 at 16:24
@Struggling1 please mark as correct by clicking the check mark by the answer.
– Scott Craner
Feb 7 at 16:35
@Struggling1 please mark as correct by clicking the check mark by the answer.
– Scott Craner
Feb 7 at 16:35
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Super User!
- 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
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%2fsuperuser.com%2fquestions%2f1403196%2fhow-do-i-extract-text-between-second-quotation-and-first-comma%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
I see three quotes before and it is the second comma in the string. Are they always that way?
– Scott Craner
Feb 7 at 16:17
Yes, all entries in the sheet are exactly the same.
– Struggling1
Feb 7 at 16:19
If they format and string length is the same, you can use the left() function. You could also use mid() or find(). P.S. I hope that's not a real person's name and address...
– spikey_richie
Feb 7 at 16:20
Also, see superuser.com/questions/1043046/…
– spikey_richie
Feb 7 at 16:22
Please Edit the question, adding tags for microsoft-excel and the specific version you're working with. The answers available may change depending on version.
– Christopher Hostage
Feb 7 at 16:32