Do you want to break text string by line break? Just follow the instructions given in this article, and you will get the result as you wish. Here we come up with the simple and quick steps to Split text string by line break in Excel Office 365. We will achieve this by using the FIND function and CHAR function. Let’s jump into this article.
Split Text String by Line Break:
To do this, we need to use a combination of the LEFT, RIGHT, CHAR, LEN, and FINDfunctions.
- Left Function – It extracts the first character (starting from the left side) or characters from the given string based on the number you specified.
- Right Function – It is used to extract the texts with the given number of characters from the right side.
- Char Function – This is used to get a character by a specified number. It converts the given number into a character.
- Len Function – It returns the length of a given text string as the number of characters.
- Find Function – This function will find out the location of the text and returns the position as a number.
Example 1:
- Refer to the below formula that will find the position of the line break character in cell B1 and extract all the characters to the left of the line break character.
=LEFT(B1,FIND(CHAR(10),B1,1)-1)
- In the below screenshot, you will see the student’s name and marks in column B.
- Apply the above-given formula in Cell C1 to get the output.
- You will see the result as shown in the below image.
Example 2:
- Refer to the below formula that will find the position of the line break character in cell B1 and extract all the characters to the right of the line break character.
=RIGHT(B1, LEN(B1)-FIND(CHAR(10),B1))
- In the below screenshot, you will see the fruit’s name and rate in column B.
- Apply the above-given formula in Cell C1 to get the output.
- You will see the result as shown in the below image.
Closure:
In the above article, we have provided the simple and quick steps to Split text string by line break in Excel Office 365. Drop your feedback/suggestions in the below comment section. Thanks for visiting Geek Excel!! Keep Learning!!
Top comments (0)