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:
=fx(cell name/cell ranges/cell references)
Example: = Sum( A1:B4)
Where Sum is the function name and A1:B4 is the cell ranges.
Visit: Cell References: Relative, Absolute and Mixed Referencing Examples
N/B: Cell name is used most of the time in text functions
Left
To extract the leftmost characters from a string, use the LEFT function.
Right
To extract the rightmost characters from a string, use the RIGHT function.
Mid
To extract a substring, starting in the middle of a string, use the MID function.
Note: started at position 5 (p) with length 3.
Len
To get the length of a string, use the LEN function.
Note: space (position 8) included!
Find
To find the position of a substring in a string, use the FIND function.
Note: string “am” found at position 3.
Substitute
To replace existing text with new text in a string, use the SUBSTITUTE function.
Join Strings
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.