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...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Name​

[/TD]
[TD]

Text​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

John​

[/TD]
[TD]

ABC 1232
DEF 456
GHI 78910​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

Mary​

[/TD]
[TD]

ABC 1232
XXX 456
CCC 1234​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

Richard​

[/TD]
[TD]

ABC 1232
XYZ 78910
ZZZ 9876​

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Lookup​

[/TD]
[TD]

Result​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

John​

[/TD]
[TD]

DEF 456​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Mary​

[/TD]
[TD]

XXX 456​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Richard​

[/TD]
[TD]

XYZ 78910​

[/TD]
[/TR]
</tbody>[/TABLE]


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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Name​

[/td][td]

Text​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

John​

[/td][td]

ABC 1232
DEF 456
GHI 78910​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Mary​

[/td][td]

BCD 2222
XXX 456
CCC 1234​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Richard​

[/td][td]

CDE 3333
XYZ 78910
ZZZ 9876​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td]

Nth​

[/td][td]

Lookup​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td]

3​

[/td][td]

John​

[/td][td]

GHI 78910​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td][/td][td][/td][td]

1​

[/td][td]

Mary​

[/td][td]

BCD 2222​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td][/td][td][/td][td]

2​

[/td][td]

Richard​

[/td][td]

XYZ 78910​

[/td][/tr]
[/table]

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.