Formula for extracting text

[jayjay123]

Hi,

Example below, in a bank statement there are the below categories.
DISHONOUR Account Closed
DISHONOUR Refer to Cust
DISHONOUR Payment Stop
FEE Payment Stop Miscellaneous Debit
Fee Refer to Cust Miscellaenous Debit

Is there an excel formula that I can use to extract a phrase "Account Closed/Refer to Cust/Payment Stop"

Similar to this, but is there a formula...if it does not contain a specific text eg. Account Closed, then search up "Refer to Cust" or "Payment Stop"?

How to Return a Value If a Cell Contains a Specific Text in Excel - Free Excel Tutorial

This post will guide you how to return a value if a cell contains a certain number or text string in Excel. How do I check if a Cell contains specific text and then return another specific text in another cell with a formula in Excel.

www.excelhow.net

Thanks

 

[RasGhul]

Maybe Try

=IF(ISNUMBER(SEARCH("Account Closed",A1)),"Account Closed",IF(ISNUMBER(SEARCH("Refer to Cust",A1)),"Refer to Cust",IF(ISNUMBER(SEARCH("Payment Stop",A1)),"Payment Stop","")))

 

[Peter_SSs]

Could you use this?

21 08 19.xlsm
	A	B
1	Text	Extract
2	DISHONOUR Account Closed	Account Closed
3	DISHONOUR Refer to Cust	Refer to Cust
4	DISHONOUR Payment Stop	Payment Stop
5	FEE Payment Stop Miscellaneous Debit	Payment Stop
6	Fee Refer to Cust Miscellaenous Debit	Refer to Cust
Bank Statement

Cell Formulas
Range	Formula
B2:B6	B2	=LET(ary,{"Account Closed","Refer to Cust","Payment Stop"},CONCAT(IFERROR(MID(A2,SEARCH(ary,A2),LEN(ary)),"")))

 

[jayjay123]

Maybe Try

=IF(ISNUMBER(SEARCH("Account Closed",A1)),"Account Closed",IF(ISNUMBER(SEARCH("Refer to Cust",A1)),"Refer to Cust",IF(ISNUMBER(SEARCH("Payment Stop",A1)),"Payment Stop","")))

this worked perfectly! thanks so much

 

[jayjay123]

Hi, some fields return "FALSE". Can I replace these with "blank"? Would you know how to do this? thanks

Maybe Try

=IF(ISNUMBER(SEARCH("Account Closed",A1)),"Account Closed",IF(ISNUMBER(SEARCH("Refer to Cust",A1)),"Refer to Cust",IF(ISNUMBER(SEARCH("Payment Stop",A1)),"Payment Stop","")))

 

[Peter_SSs]

Hi, some fields return "FALSE". Can I replace these with "blank"?

Did you try the other suggestion?
.. or this even shorter one?

21 08 19.xlsm
	A	B
1	Text	Extract
2	DISHONOUR Account Closed	Account Closed
3	DISHONOUR Refer to Cust	Refer to Cust
4	DISHONOUR Payment Stop	Payment Stop
5	FEE Payment Stop Miscellaneous Debit	Payment Stop
6	Fee Refer to Cust Miscellaenous Debit	Refer to Cust
7	Other text
Bank Statement

Cell Formulas
Range	Formula
B2:B7	B2	=LET(ary,{"Account Closed","Refer to Cust","Payment Stop"},FILTER(ary,ISNUMBER(SEARCH(ary,A2)),""))

 

[jayjay123]

Could you use this?

21 08 19.xlsm
	A	B
1	Text	Extract
2	DISHONOUR Account Closed	Account Closed
3	DISHONOUR Refer to Cust	Refer to Cust
4	DISHONOUR Payment Stop	Payment Stop
5	FEE Payment Stop Miscellaneous Debit	Payment Stop
6	Fee Refer to Cust Miscellaenous Debit	Refer to Cust
Bank Statement

Cell Formulas
Range	Formula
B2:B6	B2	=LET(ary,{"Account Closed","Refer to Cust","Payment Stop"},CONCAT(IFERROR(MID(A2,SEARCH(ary,A2),LEN(ary)),"")))

Thanks so much for your response and taking the time to help me. I tried the formula but its returning #NAME?

 

[Peter_SSs]

I tried the formula but its returning #NAME?

That most likely means that you do not yet have the LET function (some 365 subscribers have it but not all). In that case you could give this one a try as you should at least have the FILTER function.

21 08 19.xlsm
	A	B
1	Text	Extract
2	DISHONOUR Account Closed	Account Closed
3	DISHONOUR Refer to Cust	Refer to Cust
4	DISHONOUR Payment Stop	Payment Stop
5	FEE Payment Stop Miscellaneous Debit	Payment Stop
6	Fee Refer to Cust Miscellaenous Debit	Refer to Cust
7	Other text
Bank Statement

Cell Formulas
Range	Formula
B2:B7	B2	=FILTER({"Account Closed","Refer to Cust","Payment Stop"},ISNUMBER(SEARCH({"Account Closed","Refer to Cust","Payment Stop"},A2)),"")

 

[jayjay123]

That most likely means that you do not yet have the LET function (some 365 subscribers have it but not all). In that case you could give this one a try as you should at least have the FILTER function.

21 08 19.xlsm
	A	B
1	Text	Extract
2	DISHONOUR Account Closed	Account Closed
3	DISHONOUR Refer to Cust	Refer to Cust
4	DISHONOUR Payment Stop	Payment Stop
5	FEE Payment Stop Miscellaneous Debit	Payment Stop
6	Fee Refer to Cust Miscellaenous Debit	Refer to Cust
7	Other text
Bank Statement

Cell Formulas
Range	Formula
B2:B7	B2	=FILTER({"Account Closed","Refer to Cust","Payment Stop"},ISNUMBER(SEARCH({"Account Closed","Refer to Cust","Payment Stop"},A2)),"")

Thanks for the alternative formula, really appreciate it! Not sure why its not working for me unfortunately.
This one (below) works. But I would also like to retrieve a "Blank" cell if its not part of those 3 categories. Would you know any way to amend the formula to incorporate that?

RasGhul said:
Maybe Try

=IF(ISNUMBER(SEARCH("Account Closed",A1)),"Account Closed",IF(ISNUMBER(SEARCH("Refer to Cust",A1)),"Refer to Cust",IF(ISNUMBER(SEARCH("Payment Stop",A1)),"Payment Stop","")))

 

[Peter_SSs]

Thanks for the alternative formula, really appreciate it! Not sure why its not working for me unfortunately.

In what way does it not work now? Is it still #NAME?
If so that would indicate that you do not have Microsoft 365 like your profile says.

This one (below) works. But I would also like to retrieve a "Blank" cell if its not part of those 3 categories. Would you know any way to amend the formula to incorporate that?

This already returns blank if none of those three categories are found - see column B below.
If you want it to return the text "Blank", then include that in the final quote marks in the formula - see column C below.

21 08 19.xlsm
	A	B	C
1	Text	Extract
2	DISHONOUR Account Closed	Account Closed	Account Closed
3	DISHONOUR Refer to Cust	Refer to Cust	Refer to Cust
4	DISHONOUR Payment Stop	Payment Stop	Payment Stop
5	FEE Payment Stop Miscellaneous Debit	Payment Stop	Payment Stop
6	Fee Refer to Cust Miscellaenous Debit	Refer to Cust	Refer to Cust
7	Other text		Blank
8			Blank
Bank Statement

Cell Formulas
Range	Formula
B2:B8	B2	=IF(ISNUMBER(SEARCH("Account Closed",A2)),"Account Closed",IF(ISNUMBER(SEARCH("Refer to Cust",A2)),"Refer to Cust",IF(ISNUMBER(SEARCH("Payment Stop",A2)),"Payment Stop","")))
C2:C8	C2	=IF(ISNUMBER(SEARCH("Account Closed",A2)),"Account Closed",IF(ISNUMBER(SEARCH("Refer to Cust",A2)),"Refer to Cust",IF(ISNUMBER(SEARCH("Payment Stop",A2)),"Payment Stop","Blank")))