Relevant answer
Neda Ziaee you can use the following formula as well, if you need a spill array otherwise the first one works but needs to fill down
• Formula used in cell D2
=TEXTBEFORE(TEXTAFTER(E2:E5,":")," ")
Relevant answer
Thomas Dejong also you could use the following formulas where you don't have to drag down or add another formula for the length, this works with MS365. It is one single dynamic array formula it #SPILLS!
• Formula used in cell B2
=DROP(REDUCE("",A2:A8,LAMBDA(x,y,VSTACK(x,LET(a,TEXTJOIN("-",,TEXTSPLIT(y,{"kp ","-kp "})),b,TEXTAFTER(a,"-")-TEXTBEFORE(a,"-"),HSTACK(a,b))))),1)
Relevant answer
Mustafa's formula will only exclude "cancelled' if it's wrote down in Last Position of a string. But if you want to exclude all 'Cancelled' wording no matter where it's being written off, then use SEARCH
Relevant answer
try: =-LOOKUP(0,-RIGHT(A2,ROW($1:$10)))
try: =TEXTAFTER(A2:A6,{" "," - ","-"})
Relevant answer
=MAP($A$4:$A$10,LAMBDA(x,SUM(IFERROR(--TEXTAFTER(FILTER(TEXTSPLIT(x,,"|"),ISNUMBER(SEARCH(C$3,TEXTSPLIT(x,,"|")))),"qty"),0))))
Relevant answer
Try: =FILTERXML("","//b[last()-1]")
MS365 =@TAKE(TEXTSPLIT(E9,", "),-2)
Relevant answer
try, if u're using office 365.. =IF(TEXTAFTER(A2:A19,"-",,,1)<="1",B2:B19,0)
non 365 excel version.. =IF(ISERR(1/(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",255)),255)+0>1)),B2,0)
Relevant answer
Armie Rico did i mention it is my YT Channel, what is wrong here i really dont understand.
Relevant answer
Sakeer Babu Now a days it is very important to inform that "What is your Excel Version "?