How to count messages or search for a word in a text conversation
Export the conversation to CSV and open it in Excel, Numbers or Google Sheets, where a filter and a pivot table can count messages by day, month or sender. To find a word, use Find or a filter on the text column, and to count it, use a formula such as COUNTIF with wildcards.
Counting messages
- Export to CSV with TextExhibit and open it in a spreadsheet.
- Add a column that holds only the date, with a formula that takes the date part from the time column.
- Insert a pivot table with the date column as rows and a count of messages as values.
- Add the sender column as columns to count by person.
Finding and counting a word
- Filter the text column with "contains" and type the word.
- Count matches with =COUNTIF(range,"*word*").
- Use a second filter for the sender if you want one person's use of the word.
- Case does not matter in most spreadsheet search by default.
Be careful what you conclude
Counts show activity, not meaning. A busy day can mean an argument or a long planning chat. Messages deleted before the backup are not counted. If you present numbers, say the source, the date range and how you counted them, and keep the original CSV. See also exporting to Excel or CSV.
A simple formula for each sender
If the sender column is B, you can count one person's messages with =COUNTIF(B:B,"Jordan") and the total with =COUNTA(B:B)-1 to skip the header row. Divide one by the other for a percentage. Do the same for a month by adding a helper column that holds the month text and using COUNTIFS with two conditions. Spot-check the answer by filtering and reading the status bar, which in Excel and Numbers shows how many rows are visible.
What to keep in mind
- The CSV is one conversation. Counting across threads means combining files.
- Time zone affects which day a message falls on. See time zones.
Questions
Can I make a chart?
Yes, from a pivot table in any of the three spreadsheet apps.
Can the app count for me?
The preview shows the message count for the range. For finer counts, use the CSV.
Do reactions count as messages?
Check the CSV columns before you count.
Why is my count off by one?
The header row. Subtract it.
Does COUNTIF care about capital letters?
No, it ignores case.
Export the whole conversation
TextExhibit turns a conversation into one PDF with exact times, names and numbers, photos in place and page numbers, on your Mac. Free to preview; $24.99 once to export.