Training By Himanshu

Tag: Excel Formula Tutorial

  • 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

  • Exploring LEFT Function in Text Manipulation

    Exploring LEFT Function in Text Manipulation

    The LEFT function is a widely used text manipulation function in various programming languages and spreadsheet applications, such as Excel. Its primary purpose is to extract a specified number of characters from the left side of a text string. The syntax of the LEFT function typically involves providing the text string and the number of characters to be extracted from the left.

    Syntax:

    LEFT(text, num_chars)
    • text: This is the text string from which you want to extract characters.
    • num_chars: This argument specifies the number of characters to extract from the left side of the text string.

    Basic Usage:

    Let’s start by looking at the basic usage of the LEFT function with a simple example.

    Example 1:

    =LEFT("Hello, World!", 5)

    In this example, the text string is “Hello, World!” and we want to extract the first 5 characters from the left. The result of this formula would be “Hello.”

    Explanation:

    • The LEFT function takes the text string “Hello, World!” as its first argument.
    • The second argument, 5, specifies that we want to extract 5 characters from the left side of the text.
    • As a result, the function returns “Hello,” which is the first 5 characters of the input text.

    Additional Parameters:

    The LEFT function can also be used with dynamic or variable values. For example, you can reference a cell that contains the desired number of characters to be extracted.

    Example 2:

    =LEFT(A1, B1)

    In this case:

    • A1 contains the text string, e.g., “Data Processing.”
    • B1 contains the number of characters to be extracted, e.g., 4.

    The result of this formula would be “Data.”

    Nesting LEFT Function:

    The LEFT function can be nested within other functions to perform more complex text manipulations. Nesting involves using the result of one LEFT function as the input for another.

    Example 3:

    =LEFT(LEFT("Nested Example", 6), 4)

    In this example:

    • The inner LEFT function extracts the first 6 characters from the text “Nested Example,” resulting in “Nested.”
    • The outer LEFT function then extracts the first 4 characters from the result of the inner function.
    • The final result is “Nest.”

    Practical Examples with Nesting:

    Example 4:

    =LEFT(CONCATENATE("First", " ", "Last"), 5)

    Here, the CONCATENATE function combines the strings “First” and “Last” with a space in between. The LEFT function then extracts the first 5 characters from the concatenated result. The output is “First.”

    Example 5:

    =LEFT(MID("Nested Example", 3, 6), 4)

    In this case:

    • The MID function extracts a substring from “Nested Example” starting from the 3rd character and spanning 6 characters, resulting in “sted E.”
    • The LEFT function then extracts the first 4 characters from the result of the MID function, yielding “sted.”

    Example 6:

    =LEFT(IF(A1="Condition", "True Result", "False Result"), 6)

    Here, the IF function checks a condition in cell A1. If the condition is true, it returns “True Result”; otherwise, it returns “False Result.” The LEFT function then extracts the first 6 characters from the result of the IF function.

    Summary:

    LEFT function is a valuable tool for text manipulation in various applications. Its ability to extract a specified number of characters from the left side of a text string makes it versatile for tasks such as data cleaning, formatting, and analysis. Additionally, when combined with other functions and nested within formulas, the LEFT function can be part of more advanced and customized text processing operations.