Seperating multiple strings in excel cell
I'm searching for an excel formula (VBA isn't an option for this scenario) that could split two text strings that appear in a single cell.
Each string can be of any length and contain spaces, but will be seperated by two or more spaces. The first string will almost always be preceeded by multiple spaces, too.
So for example, you may have cells with values like:
north wing second floor
south korea dosan-park
From which I'd want to extract north wing and second floor as two seperate cells from the first line and from the second line get south korea and dosan-park
Any thoughts? Many thanks in advance!
excel string trim
edited Nov 14 '18 at 18:15
tigeravatar
21.1k42234
asked Nov 14 '18 at 18:05
HarperStarlingHarperStarling
83
add a comment |
I'm searching for an excel formula (VBA isn't an option for this scenario) that could split two text strings that appear in a single cell.
Each string can be of any length and contain spaces, but will be seperated by two or more spaces. The first string will almost always be preceeded by multiple spaces, too.
So for example, you may have cells with values like:
north wing second floor
south korea dosan-park
From which I'd want to extract north wing and second floor as two seperate cells from the first line and from the second line get south korea and dosan-park
Any thoughts? Many thanks in advance!
excel string trim
edited Nov 14 '18 at 18:15
tigeravatar
21.1k42234
asked Nov 14 '18 at 18:05
HarperStarlingHarperStarling
83
1
Seems like you need to look at SEARCH(), LEFT(), RIGHT() and probably also TRIM()
– Tim Williams
Nov 14 '18 at 18:11
The trick here is consistancy with strings. The two examples provided can be split by the second instance ofSpace. The more variance you strings have, the less likely you will be able to apply some catch all rule to them though. Double check and triple check that you can identify a rule of thumb that will work for ALL possible string structures
– urdearboy
Nov 14 '18 at 18:15
Thanks Urdearboy, this helped me realise the best possible answer - on all cells, there is a random number of characters in string 1 and 2 and in the spaces before/after... but there is always exactly 34 spaces to start. I was able use to a combination of SEARCH and MID to resolve. So if Cell A has the combined strings, then the following two formulas can be used: [[ =MID(A1,35,(SEARCH(" ",MID(A1,34,256)))) ]] and [[ =TRIM(MID(A1,((SEARCH(" ",MID(A1,34,256)))+34),256)) ]]
– HarperStarling
Nov 16 '18 at 14:49
add a comment |
I'm searching for an excel formula (VBA isn't an option for this scenario) that could split two text strings that appear in a single cell.
Each string can be of any length and contain spaces, but will be seperated by two or more spaces. The first string will almost always be preceeded by multiple spaces, too.
So for example, you may have cells with values like:
north wing second floor
south korea dosan-park
From which I'd want to extract north wing and second floor as two seperate cells from the first line and from the second line get south korea and dosan-park
Any thoughts? Many thanks in advance!
excel string trim
edited Nov 14 '18 at 18:15
tigeravatar
21.1k42234
asked Nov 14 '18 at 18:05
HarperStarlingHarperStarling
83
I'm searching for an excel formula (VBA isn't an option for this scenario) that could split two text strings that appear in a single cell.
Each string can be of any length and contain spaces, but will be seperated by two or more spaces. The first string will almost always be preceeded by multiple spaces, too.
So for example, you may have cells with values like:
north wing second floor
south korea dosan-park
From which I'd want to extract north wing and second floor as two seperate cells from the first line and from the second line get south korea and dosan-park
Any thoughts? Many thanks in advance!
excel string trim
excel string trim
edited Nov 14 '18 at 18:15
tigeravatar
21.1k42234
asked Nov 14 '18 at 18:05
HarperStarlingHarperStarling
83
edited Nov 14 '18 at 18:15
tigeravatar
21.1k42234
asked Nov 14 '18 at 18:05
HarperStarlingHarperStarling
83
edited Nov 14 '18 at 18:15
tigeravatar
21.1k42234
edited Nov 14 '18 at 18:15
tigeravatar
21.1k42234
edited Nov 14 '18 at 18:15
tigeravatar
21.1k42234
21.1k42234
asked Nov 14 '18 at 18:05
HarperStarlingHarperStarling
83
asked Nov 14 '18 at 18:05
HarperStarlingHarperStarling
83
asked Nov 14 '18 at 18:05
HarperStarlingHarperStarling
83
83
1
Seems like you need to look at SEARCH(), LEFT(), RIGHT() and probably also TRIM()
– Tim Williams
Nov 14 '18 at 18:11
The trick here is consistancy with strings. The two examples provided can be split by the second instance ofSpace. The more variance you strings have, the less likely you will be able to apply some catch all rule to them though. Double check and triple check that you can identify a rule of thumb that will work for ALL possible string structures
– urdearboy
Nov 14 '18 at 18:15
Thanks Urdearboy, this helped me realise the best possible answer - on all cells, there is a random number of characters in string 1 and 2 and in the spaces before/after... but there is always exactly 34 spaces to start. I was able use to a combination of SEARCH and MID to resolve. So if Cell A has the combined strings, then the following two formulas can be used: [[ =MID(A1,35,(SEARCH(" ",MID(A1,34,256)))) ]] and [[ =TRIM(MID(A1,((SEARCH(" ",MID(A1,34,256)))+34),256)) ]]
– HarperStarling
Nov 16 '18 at 14:49
add a comment |
1
Seems like you need to look at SEARCH(), LEFT(), RIGHT() and probably also TRIM()
– Tim Williams
Nov 14 '18 at 18:11
The trick here is consistancy with strings. The two examples provided can be split by the second instance ofSpace. The more variance you strings have, the less likely you will be able to apply some catch all rule to them though. Double check and triple check that you can identify a rule of thumb that will work for ALL possible string structures
– urdearboy
Nov 14 '18 at 18:15
Thanks Urdearboy, this helped me realise the best possible answer - on all cells, there is a random number of characters in string 1 and 2 and in the spaces before/after... but there is always exactly 34 spaces to start. I was able use to a combination of SEARCH and MID to resolve. So if Cell A has the combined strings, then the following two formulas can be used: [[ =MID(A1,35,(SEARCH(" ",MID(A1,34,256)))) ]] and [[ =TRIM(MID(A1,((SEARCH(" ",MID(A1,34,256)))+34),256)) ]]
– HarperStarling
Nov 16 '18 at 14:49
1
1
Seems like you need to look at SEARCH(), LEFT(), RIGHT() and probably also TRIM()
– Tim Williams
Nov 14 '18 at 18:11
Seems like you need to look at SEARCH(), LEFT(), RIGHT() and probably also TRIM()
– Tim Williams
Nov 14 '18 at 18:11
The trick here is consistancy with strings. The two examples provided can be split by the second instance of
Space. The more variance you strings have, the less likely you will be able to apply some catch all rule to them though. Double check and triple check that you can identify a rule of thumb that will work for ALL possible string structures– urdearboy
Nov 14 '18 at 18:15
The trick here is consistancy with strings. The two examples provided can be split by the second instance of
Space. The more variance you strings have, the less likely you will be able to apply some catch all rule to them though. Double check and triple check that you can identify a rule of thumb that will work for ALL possible string structures– urdearboy
Nov 14 '18 at 18:15
Thanks Urdearboy, this helped me realise the best possible answer - on all cells, there is a random number of characters in string 1 and 2 and in the spaces before/after... but there is always exactly 34 spaces to start. I was able use to a combination of SEARCH and MID to resolve. So if Cell A has the combined strings, then the following two formulas can be used: [[ =MID(A1,35,(SEARCH(" ",MID(A1,34,256)))) ]] and [[ =TRIM(MID(A1,((SEARCH(" ",MID(A1,34,256)))+34),256)) ]]
– HarperStarling
Nov 16 '18 at 14:49
Thanks Urdearboy, this helped me realise the best possible answer - on all cells, there is a random number of characters in string 1 and 2 and in the spaces before/after... but there is always exactly 34 spaces to start. I was able use to a combination of SEARCH and MID to resolve. So if Cell A has the combined strings, then the following two formulas can be used: [[ =MID(A1,35,(SEARCH(" ",MID(A1,34,256)))) ]] and [[ =TRIM(MID(A1,((SEARCH(" ",MID(A1,34,256)))+34),256)) ]]
– HarperStarling
Nov 16 '18 at 14:49
add a comment |
2 Answers
2
active
oldest
votes
From your examples, I realized that there can be many variations of the data set. To write a formula you should understand the stages and try to make it happen.
Method 1: Formulas
If your data is in cell A1 and we assume that the first part always have two separate strings then the formula is as below for the first part (example: north wing, south korea)
=LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),2))-1)
and as below for the second part (example: second floor, dosan-park)
=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),2)))
Method 2: Excel tools and formulas
The other solution I can come up with is using Excel Tools. You can take the text in one or more cells, and split it into multiple cells using the Convert Text to Columns Wizard.
Do as below:
- Select the cell or column that contains the text you want to split.
- Select Data tab, in Data Tools section click on Text to Columns tool.
- In the Convert Text to Columns Wizard, select Delimited and then click on Next.
- Select the Delimiters for your data. In your case just select Space. You can see a preview of your data in the Data preview window. Click on Next.
- Select the Column data format or use what Excel chose for you (General). Select the Destination, which is the address of your converted data. In case you want to keep your original data for any reason like comparing you should change the default destination address. I recommend you to change it to the next column address. For example, if your data is on column A and row 1 (address $A$1), you should change the destination address to $B$1. Click on Finished.
Now you have each string separated in a new cell, you can concatenate them with formulas in a new cell.
After conversion, if you have the words "north", "wing", "second", and "floor" consecutively in cells C1, D1, E1, and F1 then you can use the below formula in cell G1 to concatenate C1 and D1 to make the string "north wing"
=CONCAT(C1," ",D1)
For the second part, I used TRIM because in the case of "dosan-park" the next cell would be empty. This will add extra space at the end of "dosan-park".
=TRIM(CONCAT(E1, " ",F1))
For more clarification look at the screenshot below.
Replacing the formula with value
You can copy (Ctrl + C) the cell with the formula and use paste special (Ctrl + V) then click on the small paste icon that appears near the cell then click on V button.
answered Nov 14 '18 at 20:05
MOHAMMAD ALA AMJADIMOHAMMAD ALA AMJADI
364
Thank you! While I ultimately did not use this result (see my comments on the first post for my solution), the concept was very helpful to know and I believe would function as a solution :)
– HarperStarling
Nov 16 '18 at 14:51
add a comment |
Drop in equations and drag down as needed
B2 = TRIM(LEFT(A2,SEARCH(CHAR(32),TRIM(A2),SEARCH(CHAR(32),TRIM(A2))+1)))C2 = TRIM(RIGHT(A2,LEN(A2)-SEARCH(CHAR(32),TRIM(A2),SEARCH(CHAR(32),TRIM(A2))+1)))
answered Nov 14 '18 at 19:55
urdearboyurdearboy
7,3953730
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%2f53306331%2fseperating-multiple-strings-in-excel-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
From your examples, I realized that there can be many variations of the data set. To write a formula you should understand the stages and try to make it happen.
Method 1: Formulas
If your data is in cell A1 and we assume that the first part always have two separate strings then the formula is as below for the first part (example: north wing, south korea)
=LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),2))-1)
and as below for the second part (example: second floor, dosan-park)
=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),2)))
Method 2: Excel tools and formulas
The other solution I can come up with is using Excel Tools. You can take the text in one or more cells, and split it into multiple cells using the Convert Text to Columns Wizard.
Do as below:
- Select the cell or column that contains the text you want to split.
- Select Data tab, in Data Tools section click on Text to Columns tool.
- In the Convert Text to Columns Wizard, select Delimited and then click on Next.
- Select the Delimiters for your data. In your case just select Space. You can see a preview of your data in the Data preview window. Click on Next.
- Select the Column data format or use what Excel chose for you (General). Select the Destination, which is the address of your converted data. In case you want to keep your original data for any reason like comparing you should change the default destination address. I recommend you to change it to the next column address. For example, if your data is on column A and row 1 (address $A$1), you should change the destination address to $B$1. Click on Finished.
Now you have each string separated in a new cell, you can concatenate them with formulas in a new cell.
After conversion, if you have the words "north", "wing", "second", and "floor" consecutively in cells C1, D1, E1, and F1 then you can use the below formula in cell G1 to concatenate C1 and D1 to make the string "north wing"
=CONCAT(C1," ",D1)
For the second part, I used TRIM because in the case of "dosan-park" the next cell would be empty. This will add extra space at the end of "dosan-park".
=TRIM(CONCAT(E1, " ",F1))
For more clarification look at the screenshot below.
Replacing the formula with value
You can copy (Ctrl + C) the cell with the formula and use paste special (Ctrl + V) then click on the small paste icon that appears near the cell then click on V button.
answered Nov 14 '18 at 20:05
MOHAMMAD ALA AMJADIMOHAMMAD ALA AMJADI
364
Thank you! While I ultimately did not use this result (see my comments on the first post for my solution), the concept was very helpful to know and I believe would function as a solution :)
– HarperStarling
Nov 16 '18 at 14:51
add a comment |
From your examples, I realized that there can be many variations of the data set. To write a formula you should understand the stages and try to make it happen.
Method 1: Formulas
If your data is in cell A1 and we assume that the first part always have two separate strings then the formula is as below for the first part (example: north wing, south korea)
=LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),2))-1)
and as below for the second part (example: second floor, dosan-park)
=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),2)))
Method 2: Excel tools and formulas
The other solution I can come up with is using Excel Tools. You can take the text in one or more cells, and split it into multiple cells using the Convert Text to Columns Wizard.
Do as below:
- Select the cell or column that contains the text you want to split.
- Select Data tab, in Data Tools section click on Text to Columns tool.
- In the Convert Text to Columns Wizard, select Delimited and then click on Next.
- Select the Delimiters for your data. In your case just select Space. You can see a preview of your data in the Data preview window. Click on Next.
- Select the Column data format or use what Excel chose for you (General). Select the Destination, which is the address of your converted data. In case you want to keep your original data for any reason like comparing you should change the default destination address. I recommend you to change it to the next column address. For example, if your data is on column A and row 1 (address $A$1), you should change the destination address to $B$1. Click on Finished.
Now you have each string separated in a new cell, you can concatenate them with formulas in a new cell.
After conversion, if you have the words "north", "wing", "second", and "floor" consecutively in cells C1, D1, E1, and F1 then you can use the below formula in cell G1 to concatenate C1 and D1 to make the string "north wing"
=CONCAT(C1," ",D1)
For the second part, I used TRIM because in the case of "dosan-park" the next cell would be empty. This will add extra space at the end of "dosan-park".
=TRIM(CONCAT(E1, " ",F1))
For more clarification look at the screenshot below.
Replacing the formula with value
You can copy (Ctrl + C) the cell with the formula and use paste special (Ctrl + V) then click on the small paste icon that appears near the cell then click on V button.
answered Nov 14 '18 at 20:05
MOHAMMAD ALA AMJADIMOHAMMAD ALA AMJADI
364
Thank you! While I ultimately did not use this result (see my comments on the first post for my solution), the concept was very helpful to know and I believe would function as a solution :)
– HarperStarling
Nov 16 '18 at 14:51
add a comment |
From your examples, I realized that there can be many variations of the data set. To write a formula you should understand the stages and try to make it happen.
Method 1: Formulas
If your data is in cell A1 and we assume that the first part always have two separate strings then the formula is as below for the first part (example: north wing, south korea)
=LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),2))-1)
and as below for the second part (example: second floor, dosan-park)
=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),2)))
Method 2: Excel tools and formulas
The other solution I can come up with is using Excel Tools. You can take the text in one or more cells, and split it into multiple cells using the Convert Text to Columns Wizard.
Do as below:
- Select the cell or column that contains the text you want to split.
- Select Data tab, in Data Tools section click on Text to Columns tool.
- In the Convert Text to Columns Wizard, select Delimited and then click on Next.
- Select the Delimiters for your data. In your case just select Space. You can see a preview of your data in the Data preview window. Click on Next.
- Select the Column data format or use what Excel chose for you (General). Select the Destination, which is the address of your converted data. In case you want to keep your original data for any reason like comparing you should change the default destination address. I recommend you to change it to the next column address. For example, if your data is on column A and row 1 (address $A$1), you should change the destination address to $B$1. Click on Finished.
Now you have each string separated in a new cell, you can concatenate them with formulas in a new cell.
After conversion, if you have the words "north", "wing", "second", and "floor" consecutively in cells C1, D1, E1, and F1 then you can use the below formula in cell G1 to concatenate C1 and D1 to make the string "north wing"
=CONCAT(C1," ",D1)
For the second part, I used TRIM because in the case of "dosan-park" the next cell would be empty. This will add extra space at the end of "dosan-park".
=TRIM(CONCAT(E1, " ",F1))
For more clarification look at the screenshot below.
Replacing the formula with value
You can copy (Ctrl + C) the cell with the formula and use paste special (Ctrl + V) then click on the small paste icon that appears near the cell then click on V button.
answered Nov 14 '18 at 20:05
MOHAMMAD ALA AMJADIMOHAMMAD ALA AMJADI
364
From your examples, I realized that there can be many variations of the data set. To write a formula you should understand the stages and try to make it happen.
Method 1: Formulas
If your data is in cell A1 and we assume that the first part always have two separate strings then the formula is as below for the first part (example: north wing, south korea)
=LEFT(TRIM(A1),FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),2))-1)
and as below for the second part (example: second floor, dosan-park)
=RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(CHAR(1),SUBSTITUTE(TRIM(A1)," ",CHAR(1),2)))
Method 2: Excel tools and formulas
The other solution I can come up with is using Excel Tools. You can take the text in one or more cells, and split it into multiple cells using the Convert Text to Columns Wizard.
Do as below:
- Select the cell or column that contains the text you want to split.
- Select Data tab, in Data Tools section click on Text to Columns tool.
- In the Convert Text to Columns Wizard, select Delimited and then click on Next.
- Select the Delimiters for your data. In your case just select Space. You can see a preview of your data in the Data preview window. Click on Next.
- Select the Column data format or use what Excel chose for you (General). Select the Destination, which is the address of your converted data. In case you want to keep your original data for any reason like comparing you should change the default destination address. I recommend you to change it to the next column address. For example, if your data is on column A and row 1 (address $A$1), you should change the destination address to $B$1. Click on Finished.
Now you have each string separated in a new cell, you can concatenate them with formulas in a new cell.
After conversion, if you have the words "north", "wing", "second", and "floor" consecutively in cells C1, D1, E1, and F1 then you can use the below formula in cell G1 to concatenate C1 and D1 to make the string "north wing"
=CONCAT(C1," ",D1)
For the second part, I used TRIM because in the case of "dosan-park" the next cell would be empty. This will add extra space at the end of "dosan-park".
=TRIM(CONCAT(E1, " ",F1))
For more clarification look at the screenshot below.
Replacing the formula with value
You can copy (Ctrl + C) the cell with the formula and use paste special (Ctrl + V) then click on the small paste icon that appears near the cell then click on V button.
answered Nov 14 '18 at 20:05
MOHAMMAD ALA AMJADIMOHAMMAD ALA AMJADI
364
answered Nov 14 '18 at 20:05
MOHAMMAD ALA AMJADIMOHAMMAD ALA AMJADI
364
answered Nov 14 '18 at 20:05
MOHAMMAD ALA AMJADIMOHAMMAD ALA AMJADI
364
answered Nov 14 '18 at 20:05
MOHAMMAD ALA AMJADIMOHAMMAD ALA AMJADI
364
364
Thank you! While I ultimately did not use this result (see my comments on the first post for my solution), the concept was very helpful to know and I believe would function as a solution :)
– HarperStarling
Nov 16 '18 at 14:51
add a comment |
Thank you! While I ultimately did not use this result (see my comments on the first post for my solution), the concept was very helpful to know and I believe would function as a solution :)
– HarperStarling
Nov 16 '18 at 14:51
Thank you! While I ultimately did not use this result (see my comments on the first post for my solution), the concept was very helpful to know and I believe would function as a solution :)
– HarperStarling
Nov 16 '18 at 14:51
Thank you! While I ultimately did not use this result (see my comments on the first post for my solution), the concept was very helpful to know and I believe would function as a solution :)
– HarperStarling
Nov 16 '18 at 14:51
add a comment |
Drop in equations and drag down as needed
B2 = TRIM(LEFT(A2,SEARCH(CHAR(32),TRIM(A2),SEARCH(CHAR(32),TRIM(A2))+1)))C2 = TRIM(RIGHT(A2,LEN(A2)-SEARCH(CHAR(32),TRIM(A2),SEARCH(CHAR(32),TRIM(A2))+1)))
answered Nov 14 '18 at 19:55
urdearboyurdearboy
7,3953730
add a comment |
Drop in equations and drag down as needed
B2 = TRIM(LEFT(A2,SEARCH(CHAR(32),TRIM(A2),SEARCH(CHAR(32),TRIM(A2))+1)))C2 = TRIM(RIGHT(A2,LEN(A2)-SEARCH(CHAR(32),TRIM(A2),SEARCH(CHAR(32),TRIM(A2))+1)))
answered Nov 14 '18 at 19:55
urdearboyurdearboy
7,3953730
add a comment |
Drop in equations and drag down as needed
B2 = TRIM(LEFT(A2,SEARCH(CHAR(32),TRIM(A2),SEARCH(CHAR(32),TRIM(A2))+1)))C2 = TRIM(RIGHT(A2,LEN(A2)-SEARCH(CHAR(32),TRIM(A2),SEARCH(CHAR(32),TRIM(A2))+1)))
answered Nov 14 '18 at 19:55
urdearboyurdearboy
7,3953730
Drop in equations and drag down as needed
B2 = TRIM(LEFT(A2,SEARCH(CHAR(32),TRIM(A2),SEARCH(CHAR(32),TRIM(A2))+1)))C2 = TRIM(RIGHT(A2,LEN(A2)-SEARCH(CHAR(32),TRIM(A2),SEARCH(CHAR(32),TRIM(A2))+1)))
answered Nov 14 '18 at 19:55
urdearboyurdearboy
7,3953730
answered Nov 14 '18 at 19:55
urdearboyurdearboy
7,3953730
answered Nov 14 '18 at 19:55
urdearboyurdearboy
7,3953730
answered Nov 14 '18 at 19:55
urdearboyurdearboy
7,3953730
7,3953730
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%2f53306331%2fseperating-multiple-strings-in-excel-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
1
Seems like you need to look at SEARCH(), LEFT(), RIGHT() and probably also TRIM()
– Tim Williams
Nov 14 '18 at 18:11
The trick here is consistancy with strings. The two examples provided can be split by the second instance of
Space. The more variance you strings have, the less likely you will be able to apply some catch all rule to them though. Double check and triple check that you can identify a rule of thumb that will work for ALL possible string structures– urdearboy
Nov 14 '18 at 18:15
Thanks Urdearboy, this helped me realise the best possible answer - on all cells, there is a random number of characters in string 1 and 2 and in the spaces before/after... but there is always exactly 34 spaces to start. I was able use to a combination of SEARCH and MID to resolve. So if Cell A has the combined strings, then the following two formulas can be used: [[ =MID(A1,35,(SEARCH(" ",MID(A1,34,256)))) ]] and [[ =TRIM(MID(A1,((SEARCH(" ",MID(A1,34,256)))+34),256)) ]]
– HarperStarling
Nov 16 '18 at 14:49