Extract Text after last character

[pto160]

I have a credit card statement and I am trying to extract text after the last specific character in this case "-" and before the text cash or credit card at the end. So in between - and cash or credit card at the end. The number of character "-" could vary in a text string.

Book1
	A	B
1		What I want
2	ABC Airines-10-07-22-John Smith Credit Card	John Smith
3	Taxi Town-10-05-22-Mary Ann Stewart Cash	Mary Ann Stewart
4	TH-Stores-10-07-22-Jane Ann Smith Cash	Jane Ann Smith
5	Beyond Dreams-11-08-22-Paul Smith Credit Card	Paul Smith
Sheet1

 

[Fluff]

How about for 365

Fluff.xlsm
	A	B
1		What I want
2	ABC Airines-10-07-22-John Smith Credit Card	John Smith
3	Taxi Town-10-05-22-Mary Ann Stewart Cash	Mary Ann Stewart
4	TH-Stores-10-07-22-Jane Ann Smith Cash	Jane Ann Smith
5	Beyond Dreams-11-08-22-Paul Smith Credit Card	Paul Smith
Data

Cell Formulas
Range	Formula
B2:B5	B2	=TEXTBEFORE(TEXTAFTER(A2,"-",-1),{"credit","cash"},,1)

 

[Rick Rothstein]

Are the only two "endings" for each cell "Credit Card" and "Cash" or are there others?

Also, is this only for your XL365 or is it also for your XL2016?

 

[alansidman]

An alternative is Power Query

Book7
	A	B	C
2	Column1		Column1.2.1.1
3	ABC Airines-10-07-22-John Smith Credit Card		John Smith
4	Taxi Town-10-05-22-Mary Ann Stewart Cash		Mary Ann Stewart
5	TH-Stores-10-07-22-Jane Ann Smith Cash		Jane Ann Smith
6	Beyond Dreams-11-08-22-Paul Smith Credit Card		Paul Smith
Sheet2

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.2", Splitter.SplitTextByEachDelimiter({"Credit Card"}, QuoteStyle.Csv, true), {"Column1.2.1", "Column1.2.2"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Column1.2.1", Splitter.SplitTextByEachDelimiter({"Cash"}, QuoteStyle.Csv, true), {"Column1.2.1.1", "Column1.2.1.2"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter2",{"Column1.2.1.1"})
in
    #"Removed Other Columns"

 

[pto160]

Thanks so much for the XL365 and Power Query Solution. This is very helpful.
I still unfortunately have Excel 2016 on my work computer. I hope they are upgrading us soon.

Rick, the data will have cash or credit card at the end.
Is there an Excel 2016 solution?

 

[Fluff]

How about

=SUBSTITUTE(SUBSTITUTE(FILTERXML("<k><m>"&SUBSTITUTE(A2,"-","</m><m>")&"</m></k>","//m[last()]")," Credit Card","")," Cash","")

 

[alansidman]

Power query is native to XL2016. It is called Get and Transform Data and is located on the DATA Tab of the Ribbon.

 

[Rick Rothstein]

Rick, the data will have cash or credit card at the end.
Is there an Excel 2016 solution?

Give this a try...
=SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",300)),300))," Credit Card","")," Cash","")

 

[pto160]

Thanks so much to everyone. These solutions work great.

 

[Fluff]

Glad we could help & thanks for the feedback.