LEN,LEFT, RIGHT, MID

Excel Text Functions Overview

  • Importance of Text Functions

    • Used for extracting data from large, inconsistent, and poorly formatted datasets

    • Critical for professionals working with such data

    • Hands-on practice is essential to understanding these functions

LEN Function

  • Syntax: LEN(text)

  • Purpose: Determines the length of a given string

    • Accepts both strings and numbers, converting any input to text

    • Outputs the total character count, including spaces

LEFT Function

  • Syntax: LEFT(text, number_of_characters)

  • Purpose: Returns a specified number of characters from the start of a string

    • Example:

    • Input: LEFT("hello", 2)

    • Output: "he"

    • Input: LEFT("hello", 3)

    • Output: "hel"

RIGHT Function

  • Syntax: RIGHT(text, number_of_characters)

  • Purpose: Returns a specified number of characters from the end of a string

    • Example:

    • Input: RIGHT("hello", 2)

    • Output: "lo"

    • Input: RIGHT("hello", 3)

    • Output: "llo"

MID Function

  • Syntax: MID(text, start_number, number_of_characters)

  • Purpose: Returns a specific number of characters from a specified position in a string

    • Example:

    • Input: MID("hello", 2, 2)

    • Output: "el"

    • Input: MID("hello", 3, 2)

    • Output: "ll"

    • Input: MID("hello", 1, 2)

    • Output: "he"

PROPER, UPPER, and LOWER Functions

  • PROPER Function: Converts text to proper case

    • Example: PROPER("hello there")

    • Output: "Hello There"

  • UPPER Function: Converts all characters in text to uppercase

    • Example: UPPER("hello")

    • Output: "HELLO"

  • LOWER Function: Converts all characters in text to lowercase

    • Example: LOWER("HELLO")

    • Output: "hello"

TRUNCATE Function

  • Purpose: Similar to rounding, it truncates a number to a specified number of digits

SEARCH and SUBSTITUTE Functions

  • SEARCH Function Syntax: SEARCH(find_text, within_text, [start_num])

  • Purpose: Finds the position of a specific substring within another string

    • Example:

    • Input: SEARCH("o", "hello")

    • Output: 5 (position of "o")

  • SUBSTITUTE Function Syntax: SUBSTITUTE(text, old_text, new_text, [instance_num])

  • Purpose: Replaces existing text within a string with new text

    • Example:

    • Input: SUBSTITUTE("happy belated birthday", "belated", "")

    • Output: "happy birthday"

REPLACE Function

  • Syntax: REPLACE(old_text, start_num, num_chars, new_text)

  • Purpose: Replaces part of a string with another string, starting at a specified position

    • Example:

    • Input: REPLACE("happy belated birthday", 7, 8, "")

    • Output: "happy birthday"

Combining Functions for Flexibility

  • To achieve dynamic output regardless of intermediate words, combinations of LEFT and RIGHT can be used:

    • Example:

    • Use LEFT to get "happy" from "happy belated Halloween"

    • Use RIGHT to capture the last part ("Halloween"), excluding intervening words

    • Through concatenation, compile the results to maintain the desired format (e.g., "happy Halloween")

  • Improved Robustness:

    • The combined formula using LEFT and RIGHT will adapt well to varying words between the main terms

Advanced Techniques

  • Using SEARCH together with REPLACE:

    • Identify positions of spaces and determine text segments to replace/retain

  • Example of finding indices of spaces:

    • Searching for the first space gives position values that guide replacements or trims in text manipulation tasks

  • Example result:

    • Using calculated values to define how many characters to replace from the original text, thus ensuring correct outputs in formatted conversions