Unlock Excel's Powerful TEXT Functions (plus some 2023 newbies)

These text functions in Excel provide a powerful toolset to manipulate and analyse text data.

By mastering these functions, users can extract insights from their text data with ease.

Click > to expand

CHAR

Returns the character that corresponds to a specified ASCII code.

CHAR(number)

Example: CHAR(65) returns "A"

CLEAN

Removes non-printable characters from a text string.

CLEAN(text)

Example: CLEAN("Hello" & CHAR(9) & "Australia") returns "HelloAustralia"

CONCATENATE

Combines multiple text strings into one.

TEXT(value, format_text)

Example: TEXT(1234.56, "$#,##0.00") returns "$1,234.56"

EXACT

Compares two text strings and returns TRUE if they are exactly the same.

EXACT(text1, text2)

Example: EXACT("Hello Australia", "Hello Australia") returns TRUE

FIND

Searches for a specific character or text string within a text string and returns the starting position of the first occurrence.

FIND(find text, within_text, [start_num])

Example: FIND("Australia", "Hello Australia") returns 7

LEFT

Extracts a specified number of characters from the left side of a text string.

LEFT(text, [num_chars])

Example: LEFT("Hello Australia", 5) returns "Hello"

LEN

Returns the length of a text string, including spaces.

LEN(text)

Example: LEN("Hello Australia") returns 15

LOWER

Converts all text in a text string to lowercase.

LOWER(text)

Example: LOWER("HELLO AUSTRALIA") returns "hello australia"

MID

Extracts a specified number of characters from the middle of a text string.

MID(text, start_num, [num_chars])

Example: MID("Hello Australia", 7, 9) returns "Australia"

PROPER

Capitalises the first letter of each word in a text string.

PROPER(text)

Example: PROPER("hello australia") returns "Hello Australia"

REPT

Repeats a text string a specified number of times.

REPT(text, number_times)

Example: REPT("*", 5) returns "*****"

RIGHT

Extracts a specified number of characters from the right side of a text string.

RIGHT(text, [num_chars])

Example: RIGHT("Hello Australia", 9) returns "Australia"

SEARCH

Similar to FIND, searches for a specific character or text string within a text string and returns the starting position of the first occurrence. However, it is case-insensitive.

SEARCH(find_text, within_text, [start_num])

Example: SEARCH("Australia", "Hello Australia") returns 7

SUBSTITUTE

Similar to FIND, this function searches for a specific character or text string within a text string and returns the starting position of the first occurrence. However, it is case-insensitive. SUBSTITUTE(text, old_text, new_text, [instance_num]) Example: SUBSTITUTE("Hello World", "World", "Universe") returns "Hello Universe"

TEXT

Converts a value to text with a specified format.

TEXT(value, format_text)

Example: TEXT(A1,"dd-mmm-yy") returns 1-Jul-2023

TRIM

Removes any leading or trailing spaces from a text string.

TRIM(text)

Example: TRIM(" Hello Australia ") returns "Hello Australia"

UPPER

Converts all text in a text string to uppercase.

UPPER(text)

Example: UPPER("hello australia") returns "HELLO AUSTRALIA"

VALUE

Converts a text string to a numeric value.

VALUE(text)

Example: VALUE("123.45") returns 123.45

TEXTAFTER*

Returns text that occurs after given character or string. 

It is the opposite of the TEXTBEFORE function.

TEXTAFTER(text, delimiter)

Example: TEXTAFTER("Hello Australia", " ") returns "Australia"

TEXTBEFORE*

Returns text that occurs before a given character or string.

It is the opposite of the TEXTAFTER function.

TEXTBEFORE(text, delimiter)

Example: TEXTBEFORE("Hello Australia", " ") returns "Hello"

TEXTSPLIT*

Splits text strings by using column and row delimiters.

It's the same as the Text-to-Columns wizard, but in formula form.

TEXTSPLIT(text, delimiter, [occurrence_number])

Example: TEXTSPLIT("Hello Australia", " ", 2) returns "Australia"

By using these text functions in Excel, users can manipulate and analyze text data in a variety of ways. With a bit of practice, users can become proficient in using these functions to extract insights and make informed decisions based on their data.

*Only available for the latest Excel version releases.

---

Comdex Training News and Blogs 2023