Text Functions

Concatenate | Find/Search | Left/Right/Mid | Proper/Lower/Upper | Trim | Len | Replace/Substitute

Concatenate

The concatenate function is used to join two or more words or text strings together. 

Syntax: =CONCATENATE(text1, [text2], ...)  

 

Same way we can use "&" operator to concatenate two or more text strings.

 Copy the formula from here : 

• =CONCATENATE(A2," ",B2)
• =A2&" "&B2

Note: " " = Blank

Find/Search

The Find function tells the position of a text/value based on exact match in a string, however Search function doesn't works on Match case.

Syntax:

 =FIND(find_text, within_text, [start_num])

=SEARCH(find_text, within_text, [start_num])

If you see in below example, Find function gives an error as we are trying match "W" however "W" doesn't exit in the string which is in Upper case. Existing value is "w" which is in Lower case.

Search function gives the output = 7, as this doesn't work on match case.

Copy the formula from here: 

• =FIND(C1,B2)
• =SEARCH(C1,B4)

Left/Right/Mid

The Left function extract the value/ s from the left of a string. In below example: we have extract 2 values "Th" from the left.

Syntax: =LEFT(text, [num_chars])

The Right function extract the value/ s from the right of a string. In below example: we have extract 2 values "g." from the right.

 Syntax: =RIGHT(text, [num_chars])

The Mid function extract the value/ s from the middle of a string, where you have to define from which place you want to extract the value. In below example: we have extract 2 values "he" from the place 2.

Syntax: =MID(text, start_num, num_chars)

Copy the formula from here:

• =LEFT(B2,C1)
• =RIGHT(B4,C1)
• =MID(B6,2,C1)

Note: C1 = 2

Proper/Lower/Upper

The Proper function converts the text string to Proper case. See below in Cell C1.

Syntax: =PROPER(text) 

The Lower function converts the text string to Lower case. See below in Cell C4.

Syntax: =LOWER(text) 

The Upper function converts the text string to Upper case. See below in Cell C6.

Syntax: =UPPER(text) 

Copy the formula from here:

• =PROPER(B2)
• =LOWER(B4)
• =UPPER(B6)

Trim

The Trim function removes the extra spaces from a text string, whether it is in start, end or center.

Syntax: =TRIM(text) 

See below image: Cell B2 contained multiple spaces, however in Cell C2 formula removed all the extra space.

Copy the formula from here: =TRIM(B2)

Len

The Len function tells the length of a text String or Value.

Syntax: =LEN(text) 

See below image: In Cell C2, I have used Len function and this told us the length of the text in Cell B2, which is King.

Copy the formula from here: =LEN(B2)

Replace/Substitute

The Replace function replace the value from a string based on the position.

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

The Substitute function replace the value from a string just typing the existing value we want to replace and new value.

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

See below example:

Copy the formula from here:

• =REPLACE(B2,1,2,"s")
• =SUBSTITUTE(B4,"wa","I")

----------------------------------------------------------------------------------------------------------------------------------------------

Download the file: TextFunctions.xlsx