Saturday, November 23

Extract Text Using Left – Right – Find – and LEN Function In Excel

For extracting specific text from a particular sentence or a word please follow the step shown below.

TO EXTRACT FROM LEFT

  • Here we have a list of Emails in the excel sheet and we want to find and extract the text before the character @ from the email id Example01@hHotmail.com
  • For that we need 2 function LEFT and FIND
  • LEFT FUNCTION Looks and start from left side.
  • FIND FUNCTION find the specific character from the reference text.
  • Use Formula =LEFT(A2,FIND(“@”,A2,1) But this will extract the character “@” too. Result = Example01@
  • But not to worry for that simply minus (-) 1 character. Formula =LEFT(A2,FIND(“@”,A2,1)-1) Result = Example01
  • For example please see the image and video provided below.

TO EXTRACT FROM RIGHT

  • Here we have a list of Emails in the excel sheet and we want to find and extract the text after the character @ from the email id Example01@hHotmail.com
  • For that we need 3 function RIGHT – LEN and FIND
  • RIGHT FUNCTION Looks and start from right side.
  • LEN FUNCTION find the total length of the character in the sentence or a word.
  • FIND FUNCTION find the specific character from the reference text.
  • Use Formula =RIGHT(A2,LEN(A2)-FIND(“@”,A2,1)) Result = hHotmail.com


Leave a Reply

Your email address will not be published. Required fields are marked *