I currently have 2 columns in Excel. Multiple rows of data with line breaks.

Data (space between values equals a line break (CHAR(10)):

What I am trying to do is find which line the number '3' is on in coumn 1. Then I need the contents from cell 2 that is on that same line.

I was thinking a VBA formula that finds how many line breaks are before the 3 in column 1 and then searches column 2 for that many X line breaks and then extracts the next 10 characters from column 2 into its own cell.

I don't know VBA, but I have tried writing multiple Excel formulas that arent really giving me the exact results I need

CodePudding user response：

Let me start with that the question was fairly unclear. You referenced the cell containing several strings separated by line breaks as being a column, so at first I thought a simple VLOOKUP (=VLOOKUP(3,A:B,2,0)) would suffice.

I edited the question a little to make that more clear (and also allows people to use the data, without having to retype it to be able to reproduce).

Then I saw tinazmu's answer and thought why complicate things that much for a simpel lookup. Than looking at the pictures in her(?) post I realized it calculated single cells containing line breaks. Than looking back at your question I realized she spotted that very well.

Here's a version that would work without helper column. It does require Microsoft 365 or Excel 2021:

=LET(x, "3",
     a, TEXTSPLIT(A1,,CHAR(10)),
     b, TEXTSPLIT(B1,,CHAR(10)),
XLOOKUP(x,a,b)
     )

LET first declares names of strings/calculation results which we can refer to later in the actual formula. In this case I declared the following:

x is the value to be searched.

a is the text in cell A1 splitted by line breaks (Excel notation for line break: CHAR(10)).

b is the text in cell B1 spliited by line breaks.

Where x is a string (should be text) and a and b are both arrays of the separate strings per cell.

We can than use our formula - a simple XLOOKUP - referencing the declared names:

XLOOKUP(x,a,b)

This looks up value x in the a-array and returns the string found at the same position from the b-array.

You could also replace the search string for a cell value, for instance E4; to be able to change the search value without altering the formula. In this case it could be handy to make sure that if a number value is entered in that cell it will not result in an error while the value is actually found in the string. This happens because TEXTSPLIT is a text-function and therefore always returns text strings.

So to avoid this we implement a VALUETOTEXT and it would look like this:

=LET(x, E4,
     a, TEXTSPLIT(A1,,CHAR(10)),
     b, TEXTSPLIT(B1,,CHAR(10)),
XLOOKUP(VALUETOTEXT(x),a,b)
     )

If you want to search a range instead of a single cell we have two options. join the cell values in the range and split them the way described above:

=LET(x, E4,
     a, TEXTSPLIT(TEXTJOIN(CHAR(10),1,A1:A2),,CHAR(10)),
     b, TEXTSPLIT(TEXTJOIN(CHAR(10),1,B1:B2),,CHAR(10)),
XLOOKUP(VALUETOTEXT(x),a,b)
     )

but this way the character limit for textjoin/textsplit would be reached if there would be more cells in the range.

If that's the case we can involve LAMBDA-helper REDUCE:

=LET(x,E4,
     a, DROP(REDUCE(0,A1:A2,LAMBDA(old,new,VSTACK(old,TEXTSPLIT(new,,CHAR(10))))),1),
     b, DROP(REDUCE(0,B1:B2,LAMBDA(old,new,VSTACK(old,TEXTSPLIT(new,,CHAR(10))))),1),
XLOOKUP(VALUETOTEXT(x),a,b)
     )

The REDUCE-function behaves like this: you declare a start point and an array/range to "loop" through. You name these and these names can be used in a formula, just like we saw in the LET explanation at the start of the explanation of this post. The difference here is that we first mention the value/array/range (after REDUCE() and name them afterwards (after LAMBDA(). Another difference is that we can store the calculation value of the previously calculated result and call that value in the next calculation of the same formula; like a loop.

In this case for a I used the following names:

old for the starting point of REDUCE: 0 `

new for the "loop" array/range: A1:A2

The function would start at old. So it takes value 0 and because combined with VSTACK it vertically stacks that to no value that's there. So it results in 0. This result is now the replaced value for old and it'll now continue the same for the first value found in new. Which is the first found in the range A1:A2, being A1.

REDUCE will Vstack the old which resulted to 0 to the textsplit of the new, so the spill array of the textsplit of A1. and this becomes the array stored as old.

Next REDUCE will Vstack the old array to the new calculated spill array of the next in range: A2. And the next array for old becomes the stack of 0& spill of textsplit ofA1& spill of textsplit ofA2`. This would be the end value of REDUCE in this case, but if you have a larger range/array it would loop through it one by one and stack the results to one large array of strings.

Since REDUCE starts with 0 and this not being an actual value in your range, we use DROP([REDUCE-result],1) to remove/drop this value from the array.

CodePudding user response：

We can use FIND and SUBSTITUTE functions to do this, I extractred 5 chars from the other line, you need to adjust these to your sheet: