TEXTAFTER Function

Relevant answer

Mayukh Bhattacharya 1 year ago

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,":")," ")

2 Likes

Relevant answer

Mayukh Bhattacharya 1 year ago

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)

3 Likes

Relevant answer

Samir Shaker 1 year ago

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

3 Likes

Relevant answer

Van Dona 1 year ago

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)

1 Like

Hot network questions