Lookup Formula for Nth Line in Wrapped Text

[legalhustler]

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]

Not sure about a formula, but this custom function should work.

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]

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]

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]

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.