The free guide

Text Functions in Excel

If you want to manipulate text strings, Excel has many useful text functions to fulfill your needs.

Right

Use the RIGHT function to extract most right character of a text string.

Syntax =RIGHT(text,[num_chars]) where num_chars (option) is numbers of character to extract, like 7 in our above example.

Left

To extract left most character, use the LEFT function.

Mid

MID function is used to extract middle characters from a text string.

Length

Use LEN function to get the length (number of character) of a text string.

 

Turn negative to positive

Use ABS function to turn negative values to positive.

Join different text strings

CONCATENATE function is used to join different text strings.

Remove non-printable characters

Use CLEAN function for this.

Result:

Compare different text strings

EXACT function is used to compare different text strings or values and results with TRUE if strings are exactly same otherwise it will return FALSE.

Results:

Excel will show TRUE if both values match otherwise FALSE will appear.

For lower case letters

Use LOWER function to get all text strings with lower case letters.

Results:

Replace a specific text

SUBSTITUTE function is used to replace a specific text in a text string. For example, you can replace an article “a” with “the” in the text string.

Formulas:

Results:

Remove irregular spaces

Use TRIM function to remove irregular spaces between words.

Formula:

Result:

Copyright © 2016 - 2017 Explainry.com | All Rights Reserved