Lookup Formula for Nth Line in Wrapped Text

legalhustler

Well-known Member
Joined
Jun 5, 2014
Messages
1,069
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
Joined
Jul 14, 2008
Messages
2,625
Office Version
365, 2019, 2016
Platform
Windows
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
Joined
Feb 16, 2013
Messages
6,723
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
Joined
Aug 23, 2010
Messages
16,342
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
Joined
Aug 23, 2010
Messages
16,342
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.
 

Forum statistics

Threads
1,085,334
Messages
5,383,024
Members
401,812
Latest member
topherj09

Some videos you may like

This Week's Hot Topics

Top