Microsoft Excel Training Videos

Editing text in Excel using text functions
Editing text in Excel using text functions can help extract data from text and also manipulate text in many interesting and useful ways.
Text Formulas and Functions in Excel:
  • LEN returns the number of characters in a text string.
  • FIND(find_text,within_text,start_num):
    FIND finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text.
  • LEFT(text,num_chars):
    Text is the text string that contains the characters you want to extract. Num_chars specifies the number of characters you want LEFT to extract from the 'left'.
  • RIGHT(text,num_chars):
    RIGHT returns the last character or characters in a text string, based on the number of characters you specify
  • MID(text,start_num,num_chars):
    MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.
  • Using a combination of the above formulas, particularly, 'left', 'right' and 'find', a number of interesting text manipulations can be done.
Example: How to seperate first and last names from a fullname as shown in the Excel video:
  • Use the find function to find the location or position of the 'space' in the fullname
  • Then use the left function to extract the number of characters from the left from the fullname minus '1' which is taken by the 'space'. You get the first name from the full name!
  • To extract the last name first find out the length of the full name using the len(string) function in Excel
  • Next find the position of the 'space' in the full name
  • Now from the length of the fullname minus the position of the 'space'. This gives us the characters from the right that we need to extract.
    • Now use the 'right(string,number of characters from the right)' function in Excel to extract the last name.
FAQs on text functions in Excel
More reading on text manipulation in Excel
Editing text in Excel using text functions