# Lookup Formula for Nth Line in Wrapped Text

#### legalhustler

##### Well-known Member
I have the following text wrapped with a line break in a single cell:

ABC 1232
DEF 456
GHI 78910

I want a lookup formula to just return DEF 456? Can someone help.

#### lrobbo314

##### Well-known Member
Not sure about a formula, but this custom function should work.

Code:
``````Function NthLine(s As String, pos As Integer) As String
Dim AR() As String
AR = Split(s, Chr(10))
NthLine = AR(pos - 1)
End Function``````
Then the formula would look like = NthLine(A1,2)

#### FDibbins

##### Well-known Member
The line break would be Code 10 so you would need to search for CHAR(10) for the 1st stop, then find the 2nd CHAR(10) for the end

#### Marcelo Branco

##### MrExcel MVP
Maybe...

 A​ B​ C​ D​ 1​ Name​ Text​ 2​ John​ ABC 1232 DEF 456 GHI 78910​ 3​ Mary​ ABC 1232 XXX 456 CCC 1234​ 4​ Richard​ ABC 1232 XYZ 78910 ZZZ 9876​ 5​ 6​ Lookup​ Result​ 7​ John​ DEF 456​ 8​ Mary​ XXX 456​ 9​ Richard​ XYZ 78910​

<tbody>
</tbody>

Formula in D6 copied down
=TRIM(MID(SUBSTITUTE(VLOOKUP(C7,A:B,2,0),CHAR(10),REPT(" ",200)),SEARCH(CHAR(10),VLOOKUP(C7,A:B,2,0))+1,400))

Hope this helps

M.

#### Marcelo Branco

##### MrExcel MVP
Generic formula

 A​ B​ C​ D​ E​ 1​ Name​ Text​ 2​ John​ ABC 1232 DEF 456 GHI 78910​ 3​ Mary​ BCD 2222 XXX 456 CCC 1234​ 4​ Richard​ CDE 3333 XYZ 78910 ZZZ 9876​ 5​ 6​ Nth​ Lookup​ Result​ 7​ 3​ John​ GHI 78910​ 8​ 1​ Mary​ BCD 2222​ 9​ 2​ Richard​ XYZ 78910​

Criteria in C7:D9

Formula in E7 copied down
=TRIM(MID(SUBSTITUTE(CHAR(10)&VLOOKUP(D7,A:B,2,0),CHAR(10),REPT(" ",200)),C7*200,200))

M.