MS Excel Lesson 3

Excel offers a wide range of text functions that go beyond simply entering and displaying text. These functions can manipulate, analyze, and transform text data, enhancing your spreadsheets and improving data usability. Here are some popular and useful text functions and their benefits:

YOU CAN SAVE THE LESSON FILE BY CLICKING ON THE DOWNLOAD BUTTON AT THE END OF THE LESSON

TRIM, PROPER, UPPER & LOWER

In Excel, the TRIM, PROPER, UPPER, and LOWER functions are used for manipulating text within cells. Here is an explanation of each function along with examples:

1. TRIM Function

Syntax: TRIM(text)

• text: The text from which you want to remove extra spaces.

Example:

Formula: =TRIM(" Hello World ")
Result: "Hello World"
This removes all extra spaces from the text " Hello World ", leaving only single spaces between words.

2. PROPER Function

Syntax: PROPER(text)

• text: The text you want to convert to proper case.

Example:

Formula: =PROPER("hello world")
Result: "Hello World"
This converts the text "hello world" to proper case, where the first letter of each word is capitalized.

3. UPPER Function

Syntax: UPPER(text)

• text: The text you want to convert to uppercase.

Example:

Formula: =UPPER("Hello World")
Result: "HELLO WORLD"
This converts the text "Hello World" to uppercase.

4. LOWER Function

Syntax: LOWER(text)

• text: The text you want to convert to lowercase.

Example:

Formula: =LOWER("Hello World")
Result: "hello world"
This converts the text "Hello World" to lowercase.

LEFT, MID & RIGHT

In Excel, the LEFT, MID, and RIGHT functions are used to extract specific parts of a text string. Here is an explanation of each function along with examples:

1. LEFT Function

Syntax: LEFT(text, [num_chars])

• text: The text string from which you want to extract characters.
• num_chars: (Optional) The number of characters you want to extract from the left of the text string. If omitted, it defaults to 1.

Example:

Formula: =LEFT("Hello World", 5)
Result: Hello
This extracts the first 5 characters from the left of the string "Hello World".

2. MID Function

Syntax: MID(text, start_num, num_chars)

• text: The text string from which you want to extract characters.
• start_num: The position of the first character you want to extract.
• num_chars: The number of characters you want to extract from the text string.

Example:

Formula: =MID("Hello World", 7, 5)
Result: World
This extracts 5 characters starting from the 7th character of the string "Hello World".

3. RIGHT Function

Syntax: RIGHT(text, [num_chars])

• text: The text string from which you want to extract characters.
• num_chars: (Optional) The number of characters you want to extract from the right of the text string. If omitted, it defaults to 1.

Example:

Formula: =RIGHT("Hello World", 5)
Result: World
This extracts the last 5 characters from the right of the string "Hello World".

Summary

• LEFT extracts characters from the beginning (left side) of a string.
• MID extracts characters from the middle of a string based on a starting position and length.
• RIGHT extracts characters from the end (right side) of a string.

These functions are useful for parsing and manipulating text in various ways, such as extracting parts of a name, ID numbers, or codes.

SUBSTITUTE, REPLACE, SEARCH & FIND

In Excel, the SEARCH, SUBSTITUTE, REPLACE, and FIND functions are used for finding and manipulating text within strings. Here is an explanation of each function along with examples:

1. SEARCH Function

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

• find_text: The text you want to find.
• within_text: The text in which you want to search for the find_text.
• start_num: (Optional) The character number to start the search. If omitted, it defaults to 1.

Example:

Formula: =SEARCH("World", "Hello World")
Result: 7
This returns the position of the first character of the word "World" within the text "Hello World".

2. SUBSTITUTE Function

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

• text: The text or reference to a cell containing text in which you want to substitute characters.
• old_text: The text you want to replace.
• new_text: The text you want to replace old_text with.
• instance_num: (Optional) Specifies which occurrence of old_text you want to replace. If omitted, all occurrences are replaced.

Example:

Formula: =SUBSTITUTE("Hello World", "World", "Excel")
Result: Hello Excel
This replaces the word "World" with "Excel" in the text "Hello World".

3. REPLACE Function

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

• old_text: The text string you want to replace characters in.
• start_num: The position of the character in old_text you want to replace.
• num_chars: The number of characters you want to replace.
• new_text: The text you want to replace the characters with.

Example:

Formula: =REPLACE("Hello World", 7, 5, "Excel")
Result: Hello Excel
This replaces 5 characters starting from the 7th character of the string "Hello World" with "Excel".

4. FIND Function

Syntax: FIND(find_text, within_text, [start_num])

• find_text: The text you want to find.
• within_text: The text in which you want to search for the find_text.
• start_num: (Optional) The character number to start the search. If omitted, it defaults to 1.

Example:

Formula: =FIND("World", "Hello World")
Result: 7
This returns the position of the first character of the word "World" within the text "Hello World".

Summary

• SEARCH finds the position of a text string within another text string, is case-insensitive.
• SUBSTITUTE replaces occurrences of a specified text string with another text string.
• REPLACE replaces part of a text string with another text string based on a starting position and length.
• FIND finds the position of a text string within another text string, is case-sensitive.

These functions are useful for locating and modifying specific text within larger text strings, making them essential tools for text manipulation in Excel.

Other Text Functions

In Excel, the FIXED, DOLLAR, NUMBERVALUE, CONCAT, and TEXTJOIN functions are used for formatting and manipulating text and numbers within cells. Here is an explanation of each function along with examples:

1. FIXED Function

Syntax: FIXED(number, [decimals], [no_commas])

• number: The number you want to format.
• decimals: (Optional) The number of digits to the right of the decimal point. If omitted, it defaults to 2.
• no_commas: (Optional) A logical value that, if TRUE, prevents FIXED from including commas in the returned text.

Example:

Formula: =FIXED(1234.567, 2, TRUE)
Result: "1234.57"
This formats the number 1234.567 to 2 decimal places without commas.

2. DOLLAR Function

Syntax: DOLLAR(number, [decimals])

• number: The number you want to format as text with a dollar sign.
• decimals: (Optional) The number of digits to the right of the decimal point. If omitted, it defaults to 2.

Example:

Formula: =DOLLAR(1234.567, 2)
Result: "$1,234.57"
This formats the number 1234.567 as a currency value with 2 decimal places.

3. NUMBERVALUE Function

Syntax: NUMBERVALUE(text, [decimal_separator], [group_separator])

• text: The text to be converted to a number.
• decimal_separator: (Optional) The character used as the decimal separator in the text.
• group_separator: (Optional) The character used as the group separator in the text.

Example:

Formula: =NUMBERVALUE("1,234.56", ".", ",")
Result: 1234.56
This converts the text "1,234.56" to the number 1234.56 using "." as the decimal separator and "," as the group separator.

4. CONCAT Function

Syntax: CONCAT(text1, [text2], ...)

• text1, text2, ...: The text items to be concatenated.

Example:

Formula: =CONCAT("Hello", " ", "World")
Result: "Hello World"
This concatenates the text items "Hello", " ", and "World" into a single string.

5. TEXTJOIN Function

Syntax: TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

• delimiter: The text to use as a separator between each text item.
• ignore_empty: A logical value that, if TRUE, ignores empty text items.
• text1, text2, ...: The text items to be joined.

Example:

Formula: =TEXTJOIN(", ", TRUE, "Apple", "", "Banana", "Cherry")
Result: "Apple, Banana, Cherry"
This joins the text items "Apple", "Banana", and "Cherry" with ", " as the separator, ignoring the empty text item.

SAVE THE LESSON FILE BY CLICKING ON THE DOWNLOAD BUTTON ⬆️