Training By Himanshu

Tag: Excel string functions

  • How to Use the FILTER Function for Complete and Partial Text Match in Excel

    The FILTER function in Excel 365 is a powerful tool that allows you to extract rows from a dataset that meet specific criteria. It dynamically spills the matching results into adjacent cells — and is ideal for creating reports, dashboards, and searchable tables.

    🧠 FILTER Function Syntax:

    excelCopyEdit=FILTER(array, include, [if_empty])
    • array: The range you want to filter.
    • include: A logical expression that determines which rows to return.
    • if_empty: (Optional) Value to return if no match is found.

    ✅ 1. Complete Match of a Text String

    📘 Scenario:

    You have a list of employees and want to extract all rows where the department is exactly “HR”.

    📊 Sample Data:

    NameDepartment
    RohanHR
    MeenaSales
    AartiHR
    NikhilIT

    📄 Formula:

    excelCopyEdit=FILTER(A2:B5, B2:B5 = "HR", "No match found")

    🟢 Output:

    NameDepartment
    RohanHR
    AartiHR

    ✅ Exact/complete matches only — cells must exactly equal "HR".

    🔍 2. Partial Match of a Text String

    📘 Scenario:

    You want to filter all rows where the department contains the word “Sales”, including partial terms like “Sales – North”, “Sales Team”, etc.

    📊 Sample Data:

    NameDepartment
    RohanHR
    MeenaSales – East
    AartiHR
    NikhilSales Team

    📄 Formula:

    excelCopyEdit=FILTER(A2:B5, ISNUMBER(SEARCH("Sales", B2:B5)), "No match")

    🟢 Output:

    NameDepartment
    MeenaSales – East
    NikhilSales Team

    ✅ SEARCH enables case-insensitive partial matching, and ISNUMBER checks whether the text was found.

    💡 Tip:

    • Use SEARCH("text", cell) for partial, case-insensitive match.
    • Use FIND("text", cell) for partial, case-sensitive match.

    🎯 When to Use Complete vs Partial Match?

    Use CaseMatch TypeFormula Logic
    Filter all “IT” records onlyCompleteB2:B10 = "IT"
    Filter names that contain “Raj”PartialISNUMBER(SEARCH("Raj", A2:A10))

    🚀 Take Filtering Further with Excel VBA

    If you’re impressed by what Excel formulas can do, imagine being able to automate these filters, build custom forms, and generate filtered reports with one click.

    With Excel VBA, you can do all this and more — turning hours of manual work into minutes.

    🎓 Learn to Automate Excel with VBA – Without Prior Coding

    📘 Mastering Excel Automation – Excel VBA Training Course

    ✅ What You’ll Learn:

    • Automate repetitive tasks
    • Build dynamic reports
    • Create custom filters, buttons, and user forms
    • Use loops, conditions, and functions to control your spreadsheets

    🎥 42 structured videos
    🕒 4 hours 8 minutes of hands-on content
    💰 Limited-time price: ₹441 (was ₹1,299)
    📚 Lifetime access – Learn at your own pace

    🌟 Who Should Join?

    • Excel users ready to upgrade their skills
    • Professionals needing smart reporting
    • Beginners looking to enter automation

    🔗 👉 Enroll today and transform the way you use Excel

    Best selling products

  • How to Use TEXTBEFORE, TEXTAFTER, and TEXTSPLIT in Excel 365 with Real-World Examples

    📘 Overview of the Functions

    These text functions are new in Excel 365 and Excel 2021, part of the dynamic array functions family. They are useful for splitting or extracting parts of text based on delimiters (like commas, spaces, hyphens, etc.).

    🔹 1. TEXTBEFORE

    ✅ Purpose:

    Extracts the text before a specified delimiter.

    📘 Syntax:

    excelCopyEdit=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

    🔧 Scenario:

    You have email addresses in a list and want to extract usernames (text before @).

    🧪 Example:

    excelCopyEdit=TEXTBEFORE("john.doe@gmail.com", "@")

    ➡️ Result: john.doe

    🔹 2. TEXTAFTER

    ✅ Purpose:

    Extracts the text after a specified delimiter.

    📘 Syntax:

    excelCopyEdit=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

    🔧 Scenario:

    From an email, you want to extract just the domain name.

    🧪 Example:

    excelCopyEdit=TEXTAFTER("john.doe@gmail.com", "@")

    ➡️ Result: gmail.com

    🔹 3. TEXTSPLIT

    ✅ Purpose:

    Splits a text string into rows or columns using one or more delimiters.

    📘 Syntax:

    excelCopyEdit=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

    🔧 Scenario:

    You have full names like "John,Doe" and want to split them into first name and last name in two columns.

    🧪 Example:

    excelCopyEdit=TEXTSPLIT("John,Doe", ",")

    ➡️ Result:

    AB
    JohnDoe

    🔄 Combined Real-World Example

    🎯 Scenario:

    You have a product list like this:

    arduinoCopyEdit"SKU123-Apple-Fruit"
"SKU456-Banana-Fruit"

    And you want to extract:

    ABCD
    SKU456-Banana-FruitSKU456BananaFruit

    🧪 Formulas:

    To get the SKU:

    excelCopyEdit=TEXTBEFORE(A1, "-")

    To get the Fruit Name:

    excelCopyEdit=TEXTSPLIT(TEXTAFTER(TEXTBEFORE(A1,"-Fruit"), "-"), "-")

    To get the Category:

    excelCopyEdit=TEXTAFTER(A1, "-", 2)

    📝 Summary Table

    FunctionUse Case ExampleDescription
    TEXTBEFORETEXTBEFORE("file.docx", ".")Returns "file" before .
    TEXTAFTERTEXTAFTER("file.docx", ".")Returns "docx" after .
    TEXTSPLITTEXTSPLIT("John,Doe", ",")Splits into "John" and "Doe"

    ✅ Bonus: Why Use These?

    • Avoids complex combinations of LEFT, RIGHT, FIND, and LEN
    • Works dynamically on arrays and ranges
    • Simplifies text cleaning and parsing in data analysis

    Top rated products