How do I only keep the first line of text in a multi-line cell?
I have thousands of cells that contain three lines in each cell.
I only want to keep the first line and discard the rest.
For example, I only want to keep the "I need to eat to live" sentence.
Cell A1
I need to eat to live.
I live to eat.
I love to eat and live.
I hope my explanation makes sense :)
Thank you.
microsoft-excel
edited Dec 18 '18 at 10:12
PeterH
3,44332246
asked Dec 18 '18 at 9:35
Nazrul 3000
132
add a comment |
I have thousands of cells that contain three lines in each cell.
I only want to keep the first line and discard the rest.
For example, I only want to keep the "I need to eat to live" sentence.
Cell A1
I need to eat to live.
I live to eat.
I love to eat and live.
I hope my explanation makes sense :)
Thank you.
microsoft-excel
edited Dec 18 '18 at 10:12
PeterH
3,44332246
asked Dec 18 '18 at 9:35
Nazrul 3000
132
I just realised the example is shown as one line structure.
– Nazrul 3000
Dec 18 '18 at 9:36
Kindly imagine that each sentence is separated into rows. I think the formatting does not allow me to make it look like I have three rows/lines.
– Nazrul 3000
Dec 18 '18 at 9:38
1
By rows, do you mean line break within the cell ?
– PeterH
Dec 18 '18 at 9:47
Yes, line break is the word I was looking for PeterH.
– Nazrul 3000
Dec 18 '18 at 11:11
add a comment |
I have thousands of cells that contain three lines in each cell.
I only want to keep the first line and discard the rest.
For example, I only want to keep the "I need to eat to live" sentence.
Cell A1
I need to eat to live.
I live to eat.
I love to eat and live.
I hope my explanation makes sense :)
Thank you.
microsoft-excel
edited Dec 18 '18 at 10:12
PeterH
3,44332246
asked Dec 18 '18 at 9:35
Nazrul 3000
132
I have thousands of cells that contain three lines in each cell.
I only want to keep the first line and discard the rest.
For example, I only want to keep the "I need to eat to live" sentence.
Cell A1
I need to eat to live.
I live to eat.
I love to eat and live.
I hope my explanation makes sense :)
Thank you.
microsoft-excel
microsoft-excel
edited Dec 18 '18 at 10:12
PeterH
3,44332246
asked Dec 18 '18 at 9:35
Nazrul 3000
132
edited Dec 18 '18 at 10:12
PeterH
3,44332246
asked Dec 18 '18 at 9:35
Nazrul 3000
132
edited Dec 18 '18 at 10:12
PeterH
3,44332246
edited Dec 18 '18 at 10:12
PeterH
3,44332246
edited Dec 18 '18 at 10:12
PeterH
3,44332246
3,44332246
asked Dec 18 '18 at 9:35
Nazrul 3000
132
asked Dec 18 '18 at 9:35
Nazrul 3000
132
asked Dec 18 '18 at 9:35
Nazrul 3000
132
132
I just realised the example is shown as one line structure.
– Nazrul 3000
Dec 18 '18 at 9:36
Kindly imagine that each sentence is separated into rows. I think the formatting does not allow me to make it look like I have three rows/lines.
– Nazrul 3000
Dec 18 '18 at 9:38
1
By rows, do you mean line break within the cell ?
– PeterH
Dec 18 '18 at 9:47
Yes, line break is the word I was looking for PeterH.
– Nazrul 3000
Dec 18 '18 at 11:11
add a comment |
I just realised the example is shown as one line structure.
– Nazrul 3000
Dec 18 '18 at 9:36
Kindly imagine that each sentence is separated into rows. I think the formatting does not allow me to make it look like I have three rows/lines.
– Nazrul 3000
Dec 18 '18 at 9:38
1
By rows, do you mean line break within the cell ?
– PeterH
Dec 18 '18 at 9:47
Yes, line break is the word I was looking for PeterH.
– Nazrul 3000
Dec 18 '18 at 11:11
I just realised the example is shown as one line structure.
– Nazrul 3000
Dec 18 '18 at 9:36
I just realised the example is shown as one line structure.
– Nazrul 3000
Dec 18 '18 at 9:36
Kindly imagine that each sentence is separated into rows. I think the formatting does not allow me to make it look like I have three rows/lines.
– Nazrul 3000
Dec 18 '18 at 9:38
Kindly imagine that each sentence is separated into rows. I think the formatting does not allow me to make it look like I have three rows/lines.
– Nazrul 3000
Dec 18 '18 at 9:38
1
1
By rows, do you mean line break within the cell ?
– PeterH
Dec 18 '18 at 9:47
By rows, do you mean line break within the cell ?
– PeterH
Dec 18 '18 at 9:47
Yes, line break is the word I was looking for PeterH.
– Nazrul 3000
Dec 18 '18 at 11:11
Yes, line break is the word I was looking for PeterH.
– Nazrul 3000
Dec 18 '18 at 11:11
add a comment |
2 Answers
2
active
oldest
votes
Create a helper column, and use the below formula in the top row:
=TRIM(LEFT(A1,FIND(CHAR(10),A1)))
Then drag this down the whole column.
Copy all of the helper column, and paste the values.
You can then delete column A.
It works by finding CHAR(10), which is the first line break, then takes everything to the left of this.
Leaving you with just the first line form each Cell.
answered Dec 18 '18 at 9:51
PeterH
3,44332246
Thank you very much PeterH. You saved the day dear unsung hero. Have an awesome day ahead :)
– Nazrul 3000
Dec 18 '18 at 11:15
2
@Nazrul3000 happy to help, and welcome to super user ! you can accept the answer as correct by clicking the arrow to the left of the answer.
– PeterH
Dec 18 '18 at 11:29
How do I go if I want to retain the second (remove the first and third) and also retain the third (remove the first and second) line breaks?
– Nazrul 3000
Dec 20 '18 at 10:46
@Nazrul3000 I will update the answer later to include how to do these as well
– PeterH
Dec 20 '18 at 11:15
add a comment |
Selection.TextToColumns Destination:=Selection, _
DataType:=xlDelimited, _
Other:=True, _
OtherChar:= Chr(10), _
FieldInfo:=Array(Array(1, 1), _
Array(2, 9), _
Array(3, 9))
UPDATE
A little more information would be helpful
Create new macros (VBA editor - Add module - create subroutine with some name, or Macros - enter some name - Create, then insert this code into macro body). Then select a cell, a column, or a range containing 1 column only, with cells which need above truncation, and run created macro.
If some cell(s) contains more than 3 rows of text, macro will ask you for replacing of another cells content - cancel to protect your data.
You may freely edit the text and increase max possible rows in a cell by adding more values to FieldInfo.
answered Dec 18 '18 at 13:56
Akina
1,27628
A little more information would be helpful
– Albin
Dec 18 '18 at 15:57
@Worthwelle Done.
– Akina
Dec 19 '18 at 19:58
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
});
}
});
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%2f1385500%2fhow-do-i-only-keep-the-first-line-of-text-in-a-multi-line-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
Create a helper column, and use the below formula in the top row:
=TRIM(LEFT(A1,FIND(CHAR(10),A1)))
Then drag this down the whole column.
Copy all of the helper column, and paste the values.
You can then delete column A.
It works by finding CHAR(10), which is the first line break, then takes everything to the left of this.
Leaving you with just the first line form each Cell.
answered Dec 18 '18 at 9:51
PeterH
3,44332246
Thank you very much PeterH. You saved the day dear unsung hero. Have an awesome day ahead :)
– Nazrul 3000
Dec 18 '18 at 11:15
2
@Nazrul3000 happy to help, and welcome to super user ! you can accept the answer as correct by clicking the arrow to the left of the answer.
– PeterH
Dec 18 '18 at 11:29
How do I go if I want to retain the second (remove the first and third) and also retain the third (remove the first and second) line breaks?
– Nazrul 3000
Dec 20 '18 at 10:46
@Nazrul3000 I will update the answer later to include how to do these as well
– PeterH
Dec 20 '18 at 11:15
add a comment |
Create a helper column, and use the below formula in the top row:
=TRIM(LEFT(A1,FIND(CHAR(10),A1)))
Then drag this down the whole column.
Copy all of the helper column, and paste the values.
You can then delete column A.
It works by finding CHAR(10), which is the first line break, then takes everything to the left of this.
Leaving you with just the first line form each Cell.
answered Dec 18 '18 at 9:51
PeterH
3,44332246
Thank you very much PeterH. You saved the day dear unsung hero. Have an awesome day ahead :)
– Nazrul 3000
Dec 18 '18 at 11:15
2
@Nazrul3000 happy to help, and welcome to super user ! you can accept the answer as correct by clicking the arrow to the left of the answer.
– PeterH
Dec 18 '18 at 11:29
How do I go if I want to retain the second (remove the first and third) and also retain the third (remove the first and second) line breaks?
– Nazrul 3000
Dec 20 '18 at 10:46
@Nazrul3000 I will update the answer later to include how to do these as well
– PeterH
Dec 20 '18 at 11:15
add a comment |
Create a helper column, and use the below formula in the top row:
=TRIM(LEFT(A1,FIND(CHAR(10),A1)))
Then drag this down the whole column.
Copy all of the helper column, and paste the values.
You can then delete column A.
It works by finding CHAR(10), which is the first line break, then takes everything to the left of this.
Leaving you with just the first line form each Cell.
answered Dec 18 '18 at 9:51
PeterH
3,44332246
Create a helper column, and use the below formula in the top row:
=TRIM(LEFT(A1,FIND(CHAR(10),A1)))
Then drag this down the whole column.
Copy all of the helper column, and paste the values.
You can then delete column A.
It works by finding CHAR(10), which is the first line break, then takes everything to the left of this.
Leaving you with just the first line form each Cell.
answered Dec 18 '18 at 9:51
PeterH
3,44332246
answered Dec 18 '18 at 9:51
PeterH
3,44332246
answered Dec 18 '18 at 9:51
PeterH
3,44332246
answered Dec 18 '18 at 9:51
PeterH
3,44332246
3,44332246
Thank you very much PeterH. You saved the day dear unsung hero. Have an awesome day ahead :)
– Nazrul 3000
Dec 18 '18 at 11:15
2
@Nazrul3000 happy to help, and welcome to super user ! you can accept the answer as correct by clicking the arrow to the left of the answer.
– PeterH
Dec 18 '18 at 11:29
How do I go if I want to retain the second (remove the first and third) and also retain the third (remove the first and second) line breaks?
– Nazrul 3000
Dec 20 '18 at 10:46
@Nazrul3000 I will update the answer later to include how to do these as well
– PeterH
Dec 20 '18 at 11:15
add a comment |
Thank you very much PeterH. You saved the day dear unsung hero. Have an awesome day ahead :)
– Nazrul 3000
Dec 18 '18 at 11:15
2
@Nazrul3000 happy to help, and welcome to super user ! you can accept the answer as correct by clicking the arrow to the left of the answer.
– PeterH
Dec 18 '18 at 11:29
How do I go if I want to retain the second (remove the first and third) and also retain the third (remove the first and second) line breaks?
– Nazrul 3000
Dec 20 '18 at 10:46
@Nazrul3000 I will update the answer later to include how to do these as well
– PeterH
Dec 20 '18 at 11:15
Thank you very much PeterH. You saved the day dear unsung hero. Have an awesome day ahead :)
– Nazrul 3000
Dec 18 '18 at 11:15
Thank you very much PeterH. You saved the day dear unsung hero. Have an awesome day ahead :)
– Nazrul 3000
Dec 18 '18 at 11:15
2
2
@Nazrul3000 happy to help, and welcome to super user ! you can accept the answer as correct by clicking the arrow to the left of the answer.
– PeterH
Dec 18 '18 at 11:29
@Nazrul3000 happy to help, and welcome to super user ! you can accept the answer as correct by clicking the arrow to the left of the answer.
– PeterH
Dec 18 '18 at 11:29
How do I go if I want to retain the second (remove the first and third) and also retain the third (remove the first and second) line breaks?
– Nazrul 3000
Dec 20 '18 at 10:46
How do I go if I want to retain the second (remove the first and third) and also retain the third (remove the first and second) line breaks?
– Nazrul 3000
Dec 20 '18 at 10:46
@Nazrul3000 I will update the answer later to include how to do these as well
– PeterH
Dec 20 '18 at 11:15
@Nazrul3000 I will update the answer later to include how to do these as well
– PeterH
Dec 20 '18 at 11:15
add a comment |
Selection.TextToColumns Destination:=Selection, _
DataType:=xlDelimited, _
Other:=True, _
OtherChar:= Chr(10), _
FieldInfo:=Array(Array(1, 1), _
Array(2, 9), _
Array(3, 9))
UPDATE
A little more information would be helpful
Create new macros (VBA editor - Add module - create subroutine with some name, or Macros - enter some name - Create, then insert this code into macro body). Then select a cell, a column, or a range containing 1 column only, with cells which need above truncation, and run created macro.
If some cell(s) contains more than 3 rows of text, macro will ask you for replacing of another cells content - cancel to protect your data.
You may freely edit the text and increase max possible rows in a cell by adding more values to FieldInfo.
answered Dec 18 '18 at 13:56
Akina
1,27628
A little more information would be helpful
– Albin
Dec 18 '18 at 15:57
@Worthwelle Done.
– Akina
Dec 19 '18 at 19:58
add a comment |
Selection.TextToColumns Destination:=Selection, _
DataType:=xlDelimited, _
Other:=True, _
OtherChar:= Chr(10), _
FieldInfo:=Array(Array(1, 1), _
Array(2, 9), _
Array(3, 9))
UPDATE
A little more information would be helpful
Create new macros (VBA editor - Add module - create subroutine with some name, or Macros - enter some name - Create, then insert this code into macro body). Then select a cell, a column, or a range containing 1 column only, with cells which need above truncation, and run created macro.
If some cell(s) contains more than 3 rows of text, macro will ask you for replacing of another cells content - cancel to protect your data.
You may freely edit the text and increase max possible rows in a cell by adding more values to FieldInfo.
answered Dec 18 '18 at 13:56
Akina
1,27628
A little more information would be helpful
– Albin
Dec 18 '18 at 15:57
@Worthwelle Done.
– Akina
Dec 19 '18 at 19:58
add a comment |
Selection.TextToColumns Destination:=Selection, _
DataType:=xlDelimited, _
Other:=True, _
OtherChar:= Chr(10), _
FieldInfo:=Array(Array(1, 1), _
Array(2, 9), _
Array(3, 9))
UPDATE
A little more information would be helpful
Create new macros (VBA editor - Add module - create subroutine with some name, or Macros - enter some name - Create, then insert this code into macro body). Then select a cell, a column, or a range containing 1 column only, with cells which need above truncation, and run created macro.
If some cell(s) contains more than 3 rows of text, macro will ask you for replacing of another cells content - cancel to protect your data.
You may freely edit the text and increase max possible rows in a cell by adding more values to FieldInfo.
answered Dec 18 '18 at 13:56
Akina
1,27628
Selection.TextToColumns Destination:=Selection, _
DataType:=xlDelimited, _
Other:=True, _
OtherChar:= Chr(10), _
FieldInfo:=Array(Array(1, 1), _
Array(2, 9), _
Array(3, 9))
UPDATE
A little more information would be helpful
Create new macros (VBA editor - Add module - create subroutine with some name, or Macros - enter some name - Create, then insert this code into macro body). Then select a cell, a column, or a range containing 1 column only, with cells which need above truncation, and run created macro.
If some cell(s) contains more than 3 rows of text, macro will ask you for replacing of another cells content - cancel to protect your data.
You may freely edit the text and increase max possible rows in a cell by adding more values to FieldInfo.
answered Dec 18 '18 at 13:56
Akina
1,27628
edited Dec 19 '18 at 19:57
answered Dec 18 '18 at 13:56
Akina
1,27628
answered Dec 18 '18 at 13:56
Akina
1,27628
answered Dec 18 '18 at 13:56
Akina
1,27628
1,27628
A little more information would be helpful
– Albin
Dec 18 '18 at 15:57
@Worthwelle Done.
– Akina
Dec 19 '18 at 19:58
add a comment |
A little more information would be helpful
– Albin
Dec 18 '18 at 15:57
@Worthwelle Done.
– Akina
Dec 19 '18 at 19:58
A little more information would be helpful
– Albin
Dec 18 '18 at 15:57
A little more information would be helpful
– Albin
Dec 18 '18 at 15:57
@Worthwelle Done.
– Akina
Dec 19 '18 at 19:58
@Worthwelle Done.
– Akina
Dec 19 '18 at 19:58
add a comment |
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.
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%2fsuperuser.com%2fquestions%2f1385500%2fhow-do-i-only-keep-the-first-line-of-text-in-a-multi-line-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
I just realised the example is shown as one line structure.
– Nazrul 3000
Dec 18 '18 at 9:36
Kindly imagine that each sentence is separated into rows. I think the formatting does not allow me to make it look like I have three rows/lines.
– Nazrul 3000
Dec 18 '18 at 9:38
1
By rows, do you mean line break within the cell ?
– PeterH
Dec 18 '18 at 9:47
Yes, line break is the word I was looking for PeterH.
– Nazrul 3000
Dec 18 '18 at 11:11