Month: January 2021

Sometimes you may need to split or get each line from a multiple lines in a cell into separate cells like the below image.

In the above image you can see cell A2 have multiple line address . In adjacent cells each line of address is present in single cells.

Here, I will show you 3 ways to split multiple lines in a cell to separate cells.

Using text to columns to separate each line

For this first select range having multiple lines.

After selecting range click on Data tab then click on Text to Column option.

In text to columns there are three steps.

In 1st step tick on Delimited then click on Next button.

In 2nd step tick on Other option and in input box place cursor then press CTRL+J.

Pressing CTRL+J will bring line break character (visible as dot or may not be visible) in Other input box. Then click on Next button.

In 3rd step input destination range from where separated content will start. In the below image Destination is Cell B2. Click on Finish button.

If there’s already data here dialog box appears then just simply click on Ok button.

Now you are done below is the result of text to column.

Using VBA UDF to separate each line

Function getNthLine(str As Variant, lineNo As Byte) As String
  Dim strAr As Variant
  strAr = Split(str, Chr(10))
   If lineNo < 1 Or lineNo > UBound(strAr) + 1 Then
      getNthLine = ""
      Exit Function
   End If
  getNthLine = strAr(lineNo - 1)
End Function

To create above UDF press ALT+F11 to open VBA IDE. In VBA IDE go to Insert tab and click on Module to create new standard module. Now copy paste above code section in that module.

Make sure to save as your workbook either in .xlsb (binary) or .xlsm (macro) format.

After copy pasting UDF in Module you are ready to use getNthLine UDF function in Excel cell.

Cell B3 formula =getNthLine($A3,B$1)

1st input is text having multiple lines, 2nd input is which line number you want to extract.

You have just copy paste cell B3 formula in blank cells to get required output.

getNthLine($A3,B$1) will give getNthLine($A3,1) which means 1st line of cell A3 text.

similarly for 2nd line formula becomes =getNthLine($A3,C$1) will give getNthLine($A3,2) which means 2nd line fo cell A3 text and so on.

Using formula to separate multiple lines into cells

Cell B3 formula   =TRIM(MID(SUBSTITUTE($A3,CHAR(10),REPT(” “,LEN($A3))),(B$1-1)*LEN($A3)+1,LEN($A3)))

Let’s understand above formula working using small example. 

In this explanation instead of substituting line break (char(10)) with space , I will use “]” character so that working will be visually clear.

Cell B3 formula =SUBSTITUTE($A3,CHAR(10),REPT(“]”,LEN($A3)))

The above formula replaces all the line breaks represented by char(10) with “]”. REPT(“]”,LEN($A3)) is used to concatenate “]” N times which is equal to length of string (in this case 7).

Replacing line breaks with multiple times of string length is main idea or trick of this formula.

In 2nd step Mid function is applied 

Cell B3 formula  =MID(SUBSTITUTE($A3,CHAR(10),REPT(“]”,LEN($A3))),(B$1-1)*LEN($A3)+1,LEN($A3))

You can see output after applying Mid function.

Now you you just have to replace “]” with no string.

cell B3 formula =SUBSTITUTE(MID(SUBSTITUTE($A3,CHAR(10),REPT(“]”,LEN($A3))),(B$1-1)*LEN($A3)+1,LEN($A3)),”]”,””)

Since Trim only removes extra spaces hence  I can not use Trim for replacing “]”.

Due to this I have used Substitute function but logic or concept is same.

You can also watch video version of this blog in Hindi speech.

https://youtu.be/ofU2MQm3VDs