Manipulating text strings using Left, Mid, Right, Len, Substitute in Excel

Excel has many functions to offer when it comes to manipulating text strings.

Formula tab → Function Library Group → Text Function

N/B: Every formula in excel must begin with an equal sign, function then in bracket; cell name/cell ranges/cell references.

Syntax:

Example: = Sum( A1:B4)

Where Sum is the function name and A1:B4 is the cell ranges.

N/B: Cell name is used most of the time in text functions

To extract the leftmost characters from a string, use the LEFT function.

To extract the rightmost characters from a string, use the RIGHT function.

To extract a substring, starting in the middle of a string, use the MID function.

Note: started at position 5 (p) with length 3.

To get the length of a string, use the LEN function.

Note: space (position 8) included!

To find the position of a substring in a string, use the FIND function.

Note: string “am” found at position 3.

To replace existing text with new text in a string, use the SUBSTITUTE function.

To join strings, use the & operator.

Note: to insert a space, use ” ”

Important! After converting your text string, you will need to copy your result and then paste as values because it’s a formula.