DEV Community

Fedora
Fedora

Posted on

10 Excel String Functions You Need to Know (With Free Tools)

#spreadsheet

The Problem With Manual Formula Typing

If you work with data in Excel or Google Sheets, you've probably spent more time than you'd like typing out string manipulation formulas. Extract text, remove characters, find positions — these are everyday tasks that become repetitive fast.

In this guide, I'll walk through 10 essential Excel string functions with real examples and show you how to generate them instantly with free online tools.

1. LEFT — Extract Characters from the Start

Use case: Get the first 3 characters from a product code.

=LEFT(A2, 3)
Enter fullscreen mode Exit fullscreen mode

This pulls the first N characters from a text string. Perfect for extracting country codes, SKUs, or initials.

🔧 Try it free — Remove First 3 Characters Tool

2. RIGHT — Extract Characters from the End

Use case: Get the last 4 digits of a phone number.

=RIGHT(A2, 4)
Enter fullscreen mode Exit fullscreen mode

The opposite of LEFT — useful when you need the tail end of a string, like file extensions or postal codes.

3. MID — Extract Characters from the Middle

Use case: Pull a specific portion from the middle of a text string.

=MID(A2, 5, 3)
Enter fullscreen mode Exit fullscreen mode

Extracts N characters starting from position M. Great for ISO codes embedded in longer strings.

4. LEN — Get Text Length

Use case: Count characters to validate input length.

=LEN(A2)
Enter fullscreen mode Exit fullscreen mode

Returns the character count. Combine with IF for validation:

=IF(LEN(A2)<3, "Too short", "OK")
Enter fullscreen mode Exit fullscreen mode

5. FIND / SEARCH — Locate Text Position

Use case: Find where a substring begins.

=FIND("@", A2)      ' Finds "@" in an email
=SEARCH(" ", A2)     ' Finds first space (case-insensitive)
Enter fullscreen mode Exit fullscreen mode

FIND is case-sensitive; SEARCH is not. Both return the position number.

6. SUBSTITUTE — Replace Text

Use case: Swap one text for another throughout a cell.

=SUBSTITUTE(A2, "-", "/")
Enter fullscreen mode Exit fullscreen mode

Replace dashes with slashes, or clean up inconsistent formatting across thousands of rows.

7. CONCATENATE / & — Join Text

Use case: Merge first name and last name.

=CONCATENATE(A2, " ", B2)
'=A2 & " " & B2   ' Same thing, shorter syntax
Enter fullscreen mode Exit fullscreen mode

The ampersand (&) operator is cleaner for simple joins.

8. TRIM — Remove Extra Spaces

Use case: Clean up copied data with erratic spacing.

=TRIM(A2)
Enter fullscreen mode Exit fullscreen mode

Strips leading/trailing spaces and collapses multiple spaces between words into one. Essential for data cleaning.

9. UPPER / LOWER / PROPER — Change Case

Use case: Standardize text formatting.

=UPPER(A2)     ' ALL CAPS
=LOWER(A2)     ' all lowercase
=PROPER(A2)    ' Title Case
Enter fullscreen mode Exit fullscreen mode

PROPER is especially handy for fixing names that came in all wrong cases from a data export.

10. TEXTBEFORE / TEXTAFTER (Excel 365) — Modern String Splitting

Use case: Split text by delimiter without complex formula nesting.

=TEXTBEFORE(A2, "@")      ' Gets the part before "@"
=TEXTAFTER(A2, ".")       ' Gets the part after "."
Enter fullscreen mode Exit fullscreen mode

These new functions (Excel 365 / Google Sheets) make string splitting dramatically simpler than the old LEFT/FIND combinations.

When to Use a Formula Generator Instead

For quick one-offs, typing the formula yourself is fine. But when you're building dashboards, automating reports, or teaching others, having a tool that generates the exact formula you need saves time and reduces errors.

I built GetSheetMaster specifically for this — it's a free online reference that generates Excel and Google Sheets formulas interactively. No signup required.

For example, instead of remembering the exact syntax for removing characters from text, you can visit the Remove First 3 Characters tool, enter your cell reference, and get the formula instantly.

Quick Reference Cheat Sheet

Function What It Does Example
LEFT Extract from start =LEFT(A2, 3)
RIGHT Extract from end =RIGHT(A2, 4)
MID Extract from middle =MID(A2, 5, 3)
LEN Count characters =LEN(A2)
FIND Find position (case-sensitive) =FIND("@", A2)
SEARCH Find position (case-insensitive) =SEARCH(" ", A2)
SUBSTITUTE Replace text =SUBSTITUTE(A2, "-", "/")
CONCATENATE Join text =A2 & " " & B2
TRIM Remove extra spaces =TRIM(A2)
PROPER Title case =PROPER(A2)

Final Thoughts

String manipulation is one of the most common Excel tasks — whether you're cleaning customer data, parsing filenames, or extracting metadata. Mastering these 10 functions will cover 95% of what you'll encounter.

And if you ever need the exact formula in seconds instead of digging through documentation, tools like GetSheetMaster can generate them for you on the spot.

Which string function do you use most? Let me know in the comments.

Top comments (0)