Search code examples
excelvbaexcel-formulanewlinetextbefore

Exclude newline in TEXTBEFORE formula

I want to get the buyer from the transaction.

One of the members here, Ron, provided 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

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 works, 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

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

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

Solution

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

    enter image description here

    • 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?

    enter image description here

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