Needing to return rows of data based on words that are part of a text column

Copper Contributor

Hello,

 

I have a large spreadsheet containing over 6000 rows that I exported from Quickbooks.  I want to know how much we spent on a particular event.  The name of the event, firm meeting, is included in a description field but it is not always 3 from the left or right and may have other words around it.  I tried the filter command but that did not work.  I have shown a sample of a few rows below.  Please help, doing this manually

 

            7310 Internal Travel Airfare06/30/2022Bill97666114RLisa DELTA 00678235286562 SEATTLE WA - Airfare to Annual Firm meeting in Milwaukee 9-19 - 9-21-2220000 Accounts Payable567.21 
            7310 Internal Travel Airfare07/19/2022Bill96743099RAlexis United Airlines - Flight Firm Meeting - PWM - Milwaukee - Sep 19 - Sep 2120000 Accounts Payable476.20 
            7310 Internal Travel Airfare08/29/2022Bill6778522376312819RHolly United Airlines - Firm meeting20000 Accounts Payable298.60 
            7310 Internal Travel Airfare08/29/2022Bill6778522376312819RHolly Delta Airlines - Firm meeting20000 Accounts Payable187.60 
            7310 Internal Travel Airfare09/12/2022Bill3589640410366929RJane Delta Airlines - Internal chicago trip & conference20000 Accounts Payable637.20 
6 Replies
You can do this using a regular expression. Here's a Stack Overflow thread about it.

https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel...

Then you'll need to learn how to write regular expressions. Good luck.
please show raw data and expected result。we can test together。

@peiyezhu 

 

Here is a sample of the raw data

 DateTransaction TypeNumClassMemo/DescriptionSplitAmount
            7310 Internal Travel Airfare06/30/2022Bill97666114R DELTA 00678235286562 SEATTLE WA - Airfare to Annual Firm meeting in Milwaukee 9-19 - 9-21-2220000 Accounts Payable567.21 
            7310 Internal Travel Airfare07/19/2022Bill96743099R United Airlines - Flight Firm Meeting - PWM - Milwaukee - Sep 19 - Sep 2120000 Accounts Payable476.20 
            7310 Internal Travel Airfare08/29/2022Bill6778522376312819R United Airlines - Firm meeting20000 Accounts Payable298.60 
            7310 Internal Travel Airfare08/29/2022Bill6778522376312819R Delta Airlines - Firm meeting20000 Accounts Payable187.60 
            7310 Internal Travel Airfare09/12/2022Bill3589640410366929R Delta Airlines - Internal chicago trip & conference20000 Accounts Payable637.20 
 10/13/22Bill6778522376312819R SW Airlines20000 Accounts Payable297.82

 

Here is my expected result

 

            7310 Internal Travel Airfare06/30/2022Bill97666114R DELTA 00678235286562 SEATTLE WA - Airfare to Annual Firm meeting in Milwaukee 9-19 - 9-21-2220000 Accounts Payable567.21 
            7310 Internal Travel Airfare07/19/2022Bill96743099R United Airlines - Flight Firm Meeting - PWM - Milwaukee - Sep 19 - Sep 2120000 Accounts Payable476.20 
            7310 Internal Travel Airfare08/29/2022Bill6778522376312819R United Airlines - Firm meeting20000 Accounts Payable298.60 
            7310 Internal Travel Airfare08/29/2022Bill6778522376312819R Delta Airlines - Firm meeting20000 Accounts Payable

187.60 

       

 

 

Please note the words I am looking for are "firm meeting".  There are 956 rows of data that need to be looked through and I only want the rows returned that have firm meeting in them.  The purpose is for me to be able to add up all the charges pertaining to the firm meeting.

 

Susan

@Susan_Mazany 

You can try Power Query. In the attached file you can enter data in the blue dynamic table. Then you can click in any cell of the green table and right click with the mouse and select refresh.

 

The layout of the tables in the screenshot is for illustration. You can place the green table to the right of the blue table or in another worksheet.

firm meeting.JPG

 

If you work with Excel 2019 or later you can apply the FILTER function which would be an easier solution.

=SUMPRODUCT(ISNUMBER(SEARCH("firm meeting",Tabelle2[Spalte5]))*Tabelle2[Spalte7])

You can try this formula if you only want to sum the transactions with "firm meeting".

Thank you. I will try this and let you know what happens.

=FILTER(Tabelle2,ISNUMBER(SEARCH(A17,Tabelle2[Spalte5])))