{"id":127,"date":"2019-11-23T21:39:43","date_gmt":"2019-11-23T21:39:43","guid":{"rendered":"http:\/\/pragmaticways.com\/?p=127"},"modified":"2020-01-31T23:21:21","modified_gmt":"2020-01-31T23:21:21","slug":"extract-text-in-excel-the-easy-way","status":"publish","type":"post","link":"https:\/\/pragmaticways.com\/extract-text-in-excel-the-easy-way\/","title":{"rendered":"Extract text in Excel the Easy way"},"content":{"rendered":"\n

Here are 5 quick formulas for you to extract text from cells in Excel. <\/p>\n\n\n\n

Have you ever had a problem where you need to get a specific word from a string in another cell? This type of work is referred to as Data Manipulation, and is a very important skills to learn for anyone using MS Excel. (This can help a lot when creating Pivot Tables<\/a>!)<\/p>\n\n\n\n\n\n

Extract text before first space (or comma or other character)<\/h2>\n\n\n
=LEFT(A1,(FIND(\" \"<\/span>,A1,1<\/span>)-1<\/span>))<\/code><\/div>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Note: if you need a comma or some other character instead of a space, replace the ” “<\/span> part with the character you need wrapped in double quotes. <\/p>\n
\nExtract text before first comma<\/strong>
\n=LEFT(A1,(FIND(“,”,A1,1)-1))<\/span>\n
\n
\nExtract text before first ???<\/strong>
\n=LEFT(A1,(FIND(“???”,A1,1)-1))<\/span>\n\n\n\n
This formula will extract the all the text from cell A1 that occurs before the first space. A great example of this is when you need to extract the first names from a column of full names.<\/p>\n\n\n\n
\"Snapshot<\/figure>
\n
We start with a list of Full Names in Column A, and we want to extract all the First Names to be in Column B.<\/p>\n<\/div><\/div>\n\n\n\n
\n\n\n\n
\"Snapshot<\/figure>
\n
  • Select cell B2<\/strong><\/li>
  • In the function bar, type the formula =LEFT(A2,(FIND(” “,A2,1)-1))<\/span><\/li>
  • Press the [Enter] or [Return] key<\/li><\/ul>\n<\/div><\/div>\n\n\n\n
    \n\n\n\n
    \"place<\/figure>
    \n
    To apply the formula to the entire column, place your cursor in the lower right corner of the cell until you see the little + symbol. <\/p>\n\n\n\n
    Then just double click, and watch the magic!<\/p>\n<\/div><\/div>\n\n\n\n
    \n\n\n\n
    \"extracted<\/figure>\n\n\n\n
    \n\n\n\n
    Extract text after first space (or comma or other character)<\/h2>\n\n\n
    =MID(A2,FIND(\" \"<\/span>,A2)+1<\/span>,LEN(A2))<\/code><\/div>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Note: if you need a comma or some other character instead of a space, replace the ” “<\/span> part with the character you need wrapped in double quotes. <\/p>\n
    \nExtract text after first comma<\/strong>
    \n=MID(A2,FIND(“,”,A2)+1,LEN(A2))<\/span>\n
    \n
    \nExtract text after first ???<\/strong>
    \n=MID(A2,FIND(“???”,A2)+1,LEN(A2))<\/span>\n\n\n\n
    This formula will extract the ALL the text from cell A1 that occurs after<\/strong> the first space. A great example of this is when you need to extract the last names from a column of full names.<\/p>\n\n\n\n
    \"get<\/figure>
    \n
    We start with a list of Full Names in Column A, and we want to extract all the Last Names to be in Column B.<\/p>\n<\/div><\/div>\n\n\n\n
    \n\n\n\n
    \"select<\/figure>
    \n
    • Select cell B2<\/strong><\/li>
    • In the function bar, type the formula =MID(A2,FIND(” “,A2)+1,LEN(A2))<\/span><\/li>
    • Press the [Enter] or [Return] key<\/li><\/ul>\n<\/div><\/div>\n\n\n\n
      \n\n\n\n
      \"apply<\/figure>
      \n
      To apply the formula to the entire column, place your cursor in the lower right corner of the cell until you see the little + symbol. <\/p>\n\n\n\n
      Then just double click, and watch the magic!<\/p>\n<\/div><\/div>\n\n\n\n
      \n\n\n\n
      \"get<\/figure>\n\n\n\n
      Extract text before second<\/em> space (or comma or other character)<\/h2>\n\n\n
      =LEFT(A2, SEARCH(\" \"<\/span>, A2, SEARCH(\" \"<\/span>, A2) + 1<\/span>))<\/code><\/div>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
      That complex formula above will get ALL the text from cell A2 that occurs before<\/em><\/strong> <\/em>the second<\/em><\/strong> space. <\/p>\n\n\n\n
      If you need to get a specific word from a string with commas<\/strong> instead, then just replace ” “<\/span> with “,”<\/span>. Like this formula below:<\/p>\n\n\n
      =LEFT(A2, SEARCH(\",\"<\/span>, A2, SEARCH(\",\"<\/span>, A2) + 1<\/span>))<\/code><\/div>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
      Note: the code above will include the trailing comma. If you need to strip off the last comma, then use this instead:<\/p>\n\n\n
      =LEFT(A2, SEARCH(\",\"<\/span>, A2, SEARCH(\",\"<\/span>, A2) + 1<\/span>)-1<\/span>)<\/code><\/div>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
      Pro Tip<\/strong>: Once you have the formula entered in properly, you can very quickly apply it to the entire column: (scroll up to the previous sections for “How To” <\/em>screenshots)<\/p>\n\n\n\n
      • Move cursor to bottom right hand corner of cell (with formula already entered)<\/li>
      • When you see the little + symbol, double click  <\/li><\/ul>\n\n\n\n
        Extract text after second<\/em> space (or comma or other character)<\/h2>\n\n\n
        =RIGHT(A2, LEN(A2) - (SEARCH(\" \"<\/span>, A2, SEARCH(\" \"<\/span>, A2) + 1<\/span>)))<\/code><\/div>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
        The code above will extract ALL text after<\/em><\/strong> the second <\/em><\/strong>space from cell A2. <\/p>\n\n\n\n
        If you need to get a specific word from a string with commas<\/strong> instead, then just replace ” ” with “,”. Like this formula below:<\/p>\n\n\n
        =RIGHT(A2, LEN(A2) - (SEARCH(\",\"<\/span>, A2, SEARCH(\",\"<\/span>, A2) + 1<\/span>)))<\/code><\/div>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
        Pro Tip<\/strong>: Once you have the formula entered in properly, you can very quickly apply it to the entire column: (scroll up to the previous sections for “How To” <\/em>screenshots)<\/p>\n\n\n\n
        • Move cursor to bottom right hand corner of cell (with formula already entered)<\/li>
        • When you see the little + symbol, double click  <\/li><\/ul>\n\n\n\n
          Extract text between two spaces (or two commas or two characters)<\/h2>\n\n\n
          =MID(A2,FIND(\" \"<\/span>,A2)+1<\/span>,FIND(\" \"<\/span>,A2,FIND(\" \"<\/span>,A2)+1<\/span>)-FIND(\" \"<\/span>,A2))<\/code><\/div>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
          The code above will extract the string in A2 that is between<\/strong> two spaces. This is ideal when you have a Full Name column (firstName MiddleName lastName) and need to extract only the middle name. <\/p>\n\n\n\n
          If you need to extract a string from a comma delimited cell, then you could use this formula instead:<\/p>\n\n\n
          =MID(A2,FIND(\",\"<\/span>,A2)+1<\/span>,FIND(\",\"<\/span>,A2,FIND(\",\"<\/span>,A2)+1<\/span>)-FIND(\",\"<\/span>,A2)-1<\/span>)<\/code><\/div>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
          Pro Tip<\/strong>: Once you have the formula entered in properly, you can very quickly apply it to the entire column: (scroll up to the previous sections for “How To” <\/em>screenshots)<\/p>\n\n\n\n
          • Move cursor to bottom right hand corner of cell (with formula already entered)<\/li>
          • When you see the little + symbol, double click  <\/li><\/ul>\n\n\n\n
            \n\n\n\n
            Anything I missed?<\/strong><\/p>\n\n\n\n
            Leave a comment below if there’s anything you wanted to see but didn’t. If you found this article useful, bookmark it for future reference. (And don’t forget to share it with your friends, social media, etc.) <\/p>\n\n\n\n
            If you want more useful Excel tips coming to your inbox (I promise not to spam you!) then you should signup for the free newsletter!<\/p>\n\n\n
            [sibwp_form id=2]<\/p>\n","protected":false},"excerpt":{"rendered":"
            Here are 5 quick formulas for you to extract text from cells in Excel. Have you ever had a problem where you need to get a specific word from a<\/p>\n","protected":false},"author":1,"featured_media":128,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[29],"tags":[],"_links":{"self":[{"href":"https:\/\/pragmaticways.com\/wp-json\/wp\/v2\/posts\/127"}],"collection":[{"href":"https:\/\/pragmaticways.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pragmaticways.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pragmaticways.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pragmaticways.com\/wp-json\/wp\/v2\/comments?post=127"}],"version-history":[{"count":0,"href":"https:\/\/pragmaticways.com\/wp-json\/wp\/v2\/posts\/127\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/pragmaticways.com\/wp-json\/wp\/v2\/media\/128"}],"wp:attachment":[{"href":"https:\/\/pragmaticways.com\/wp-json\/wp\/v2\/media?parent=127"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pragmaticways.com\/wp-json\/wp\/v2\/categories?post=127"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pragmaticways.com\/wp-json\/wp\/v2\/tags?post=127"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}