Exclude newline in TEXTBEFORE formula

Question

Good day,

I want to get the BUYER from the transaction. One of the member here, Ron, was kind enough to provide me with the TEXTAFTER & TEXTBEFORE formula.

Previous Transaction:

1) Peter sell 10 apples to May
2) Jon sell 3 pears to Wei Liang
3) Tom sell to William 4 oranges
4) Ellen sell 1 banana to Andy

Who is the Buyer

1) May
2) Wei Liang
3) William
4) Andy

formula to get Buyer:

=LET(ta,TEXTAFTER(A2,"to "),TEXTBEFORE(ta,{0,1,2,3,4,5,6,7,8,9},1,,,ta))

The formula work, but now I have a new type of Transaction:

1) Peter sell 10 apples to May

2) Jon sell 3 pears to Wei Liang
Note: No plastic bags

3) Tom sell to William 4 oranges
Note: Delivery within 1 day

4) Ellen sell 1 banana to Andy

Now some transactions have an additional line. So for 2) and 3), using the formula will give me the extra lines for Buyer.

How should I exclude newline in the TEXTBEFORE? I tried adding vbNewLine in the formula but got error.

Answer 1

If you are using MS365 then this can be accomplished as well using Excel Formulas:

---

• Formula used in cell B2

=LET(α, TEXTAFTER(A2:A5,"to "), TEXTBEFORE(α,HSTACK(CHAR(10),SEQUENCE(,10)-1),,,,α))

---

Even this would work, if there is Note: after each Buyer:

=LET(α, TEXTAFTER(A2:A5,"to "), TEXTBEFORE(α,HSTACK("Note:",SEQUENCE(,10)-1),,,,α))

---

Edit 3/25/2024:

As per OP's comments --

Hi Sir, I found an issue. If let's say the Seller name contains "to", like 5) Entoneal sell 4 melons to Jackson, then the buyer will become "neal sell" instead of Jackson. How do I check that "to" is not part of Seller name pls?

---

---

Formula remains same, without any single changes done to it. Screenshot updated to show its still working on my end.

---