How to Analyze Text Data in Excel

Your business runs on more than just numbers. Customer feedback, survey responses, product reviews, and even team chat logs are filled with incredibly valuable information. The challenge is that this goldmine of data is often messy, unstructured text. Trying to make sense of it can feel impossible without specialized tools, but you can actually accomplish a lot using a tool you already know and use every day: Microsoft Excel.

This tutorial will walk you through the essential Excel functions and techniques to clean, parse, and analyze text data. We’ll cover everything from tidying up inconsistent inputs to counting keywords and summarizing your findings in a pivot table.

First Things First: Why Analyze Text Data?

Before jumping into formulas, it’s helpful to understand what you’re trying to achieve. While numbers tell you what is happening (e.g., "sales dropped by 10%"), text data often tells you why it’s happening ("the checkout process was confusing," "shipping was too slow").

Here are a few common scenarios where text analysis in Excel is incredibly useful:

• Customer Surveys: Analyzing open-ended feedback to identify common themes, feature requests, or points of confusion.
• Support Tickets: Categorizing issues to spot recurring problems and common complaints.
• Product Reviews: Finding out what customers love or hate about specific product features.
• Marketing Copy: Standardizing campaign names or product descriptions from different sources.

Step 1: Get Your Data Ready for Analysis (Cleaning)

Raw text data is rarely usable right away. It's often inconsistent, with extra spaces, mixed capitalization, and other formatting quirks that can ruin your analysis. Cleaning is the foundational step that makes everything else possible.

Remove Extra Spaces with TRIM

Leading, trailing, and double spaces between words can make two identical text entries appear different to Excel. The TRIM function is your best friend for fixing this.

Imagine a column of survey responses where some entries have accidental spaces before or after the text. TRIM removes all these extra spaces, leaving just a single space between words.

• Formula: =TRIM(A2)
• Example: If cell A2 contains " Great service! ", the formula returns "Great service!".

Standardize Casing with UPPER, LOWER, and PROPER

Inconsistent capitalization - like "USA," "Usa," and "usa" - can fracture your data into separate categories. Use these three functions to standardize everything.

• UPPER: Converts all letters to uppercase (e.g., "new york" becomes "NEW YORK").
• LOWER: Converts all letters to lowercase (e.g., "New York" becomes "new york").
• PROPER: Capitalizes the first letter of each word (e.g., "new york" becomes "New York"). This is great for names and places.

Pro Tip: Combine Formulas

You can nest formulas to perform multiple cleaning actions at once. For example, to standardize capitalization and remove extra spaces from a messy entry, you can combine PROPER and TRIM.

• Formula:

Step 2: Split and Join Text Columns

Often, a single cell contains multiple pieces of information you want to analyze separately, like a full name you want to split into "First Name" and "Last Name." Other times, you need to combine separate cells into one.

Splitting Text into Multiple Columns

The easiest way to do this is with Excel’s built-in Text to Columns feature.

1. Select the column of data you want to split.
2. Go to the Data tab and click Text to Columns.
3. Choose Delimited if your text is separated by a character like a comma, space, or hyphen. Choose Fixed Width if the data fields are aligned in columns with spaces between each field.
4. If you chose Delimited, check the box for the delimiter in your text (e.g., Space).
5. Follow the prompts to finish the process. Your data will be split into new columns to the right.

For a more dynamic approach that updates automatically if the source data changes, use the TEXTSPLIT function (available in newer Excel versions).

• Formula:

This splits the text in A2 into separate columns wherever it finds a space.

Combining Text with CONCATENATION

The opposite of splitting is joining, or concatenating. The simplest method is using the ampersand (&) operator.

To combine a first name in A2 and a last name in B2 into a full name, use:

• Formula:

This tells Excel to take the value from A2, add a space (" "), and then add the value from B2. The result might be "John Smith".

You can also use the CONCAT or CONCATENATE function, but most people find the & symbol quicker and easier to read.

Step 3: Find and Extract Specific Pieces of Text

Now we get to the more surgical analysis — pulling out specific bits of text from a larger string. This is invaluable for extracting product codes, email domains, or specific keywords.

LEFT, RIGHT, and MID Functions

These three functions are the workhorses of text extraction.

• LEFT(text, num_chars): Extracts a specified number of characters from the start of a text string.
• RIGHT(text, num_chars): Extracts a specified number of characters from the end of a text string.
• MID(text, start_num, num_chars): Extracts characters from the middle of a text string, starting at a position you define.

Example: Imagine you have product SKUs in the format CATEGORY-PRODUCT-SIZE (e.g., SHIRT-408-L). Here's how you'd extract each part:

• Get the category: =LEFT(A2, 5) would return "SHIRT-". We can do better!
• Get the size: =RIGHT(A2, 1) would return "L".

Making Extractions Dynamic with FIND

Hard-coding the number of characters (like the "5" above) isn't flexible. What if the next category is "SWEATER," which has more letters? The FIND function solves this by locating the position of a specific character.

• FIND(find_text, within_text) returns the starting position of a character or text string. For example, FIND("-", "SHIRT-408-L") would return 6, since the hyphen is the 6th character.

Now, let’s combine it with our extraction functions for a truly dynamic formula:

• Extract the category (letters before the first hyphen):

This finds the position of the hyphen and then extracts all characters to the left of it, giving you "SHIRT".

Step 4: Quantify Your Findings with COUNTIF

Once your data is cleaned and structured, you can start counting things. This is how you turn text observations into quantifiable insights. The COUNTIF function is perfect for this task.

It counts the number of cells within a range that meet a specific condition.

• Formula:

For example, suppose you have a column (A2:A100) of customer feedback comments. You want to know how many people mentioned the word "slow".

• Formula:

The asterisks (*) are wildcards that mean "any number of other characters." So, "slow" will count cells containing "slow," "slower," and "the pages were loading slowly." This helps identify key themes. You can create a simple table of keywords ("slow," "easy," "price," "confusing") and use COUNTIF for each to build a summary of common topics.

Step 5: Summarize Everything with a Pivot Table

Pivot Tables are Excel’s most powerful tool for summarizing data. While often associated with numbers, they work wonderfully with organized text data too.

Let’s use our user feedback example:

1. Add a "helper column" to your data table. Let’s say you want to categorize feedback as "Performance," "Usability," or "Pricing."
2. In this new column, you can write a formula using IF and COUNTIF to assign a category to each row. For example:
3. Once you have a category for each comment, click anywhere in your data table, go to the Insert tab, and click PivotTable.
4. In the PivotTable Fields pane, drag your new "Category" column to both the Rows and Values areas.
5. Excel will automatically "Count" the text entries, instantly giving you a summary table showing how many comments fall into each category.

Final Thoughts

Knowing your way around text functions can turn Excel into a surprisingly powerful qualitative analysis tool. By combining cleaning functions like TRIM, parsing tools like LEFT and FIND, and summary features like COUNTIF and PivotTables, you can transform messy text into clear, actionable insights without needing to learn a new, complicated piece of software.

And while Excel is a fantastic tool for this kind of work, we know that the manual process of exporting CSVs from all your different platforms and then cleaning, organizing, and building reports can burn hours every week. That’s why we built Graphed. We connect directly to your marketing and sales tools, automating the data pipeline so you can skip the spreadsheet wrangling and ask questions in plain English - like "summarize customer feedback from our last survey by theme." Graphed instantly builds the charts and summaries for you, giving you back time to focus on strategy instead of formulas.