Assume that you have a list of data that contains multiple lines, and you need to separate them into a single line. How can you solve this job? In this tutorial, we are going to see how to split the multiple lines from a cell and return them into separated rows or columns in Excel. Let’s see them below!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
Steps to Split the multiple lines:
You can use the below formula to extract the multiple lines.
=TRIM( MID(SUBSTITUTE( $B2, CHAR(10), REPT( ” “,LEN($B2) ) ), (C$1-1)*LEN($B2)+1, LEN($B2)))
Argument Explanations:
- TRIM – This function will help to remove the extra spaces from the text and there is no space at the start or end of the text string. Read more on the TRIM function.
- MID – In Excel, the MID function helps to extract the number (starting from the left side) or characters from the given string
- SUBSTITUTE – It will replace existing text with new text in a text string when you want to replace text based on its content, not position. Read more on the SUBSTITUTE Function.
- CHAR – This function helps to get a character by a specified number. Read more on the CHAR function.
- REPT – The Excel REPT function repeats characters a given number of times.
- LEN – In Excel, the LEN function returns the length of a given text string as the number of characters.
Practical Example:
- First, you need to enter the text string in multiple lines by clicking the ALT + ENTER keys. It will display the input data line by line as shown below.
- Then apply the above-given formula to the formula bar section.
- After that press the Enter key, it will split the text and return it into separate rows.
Bottom-Line:
From this short tutorial, you can understand how to split the multiple lines from a cell and return the values into separate rows in Excel. Hope you like it. Please state your query in the below comment section. Thank you so much for visiting Geek Excel!!
Top comments (0)