{"id":142,"date":"2011-08-24T18:43:41","date_gmt":"2011-08-24T18:43:41","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/exceltraining\/?p=142"},"modified":"2023-12-30T23:14:26","modified_gmt":"2023-12-30T23:14:26","slug":"microsoft-excel-2010-extract-data-from-a-cell-using-left-function","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/microsoft-excel-2010-extract-data-from-a-cell-using-left-function\/","title":{"rendered":"Microsoft Excel 2010 &#8211; Extract Data From a Cell Using LEFT Function"},"content":{"rendered":"<p>In the following example there is a column of stock codes. The numbers represent our Supplier and the letters after the hyphen (-) indicates our Item number.<\/p>\n<p><a href=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/08\/Best-STL-Training-London_Excel-LEFT-Function.jpg\"><img decoding=\"async\" class=\"alignnone size-full wp-image-143\" src=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/08\/Best-STL-Training-London_Excel-LEFT-Function.jpg\" alt=\"Excel Training LEFT Function\" width=\"352\" height=\"277\" \/><\/a><\/p>\n<p>Suppose we need to extract the Supplier and Item codes and place in separate cells. If our Supplier codes were all equal length e.g. 3 numbers long, we could simply use the LEFT function.<br \/>\nIn the <strong>Excel 2010 Training Courses<\/strong> here in <strong>London<\/strong> this is a common question.<\/p>\n<p>Example:<br \/>\n<a href=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/08\/Best-STL-Training-London_Excel-Extract-Data.jpg\"><img decoding=\"async\" class=\"alignnone size-full wp-image-144\" src=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/08\/Best-STL-Training-London_Excel-Extract-Data.jpg\" alt=\"Excel Training London Using the LEFT Function\" width=\"700\" height=\"297\" \/><\/a><\/p>\n<p>Result:<br \/>\n<a href=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/08\/Best-STL-Training-London_Excel-2010-Result.jpg\"><img decoding=\"async\" class=\"alignnone size-full wp-image-145\" src=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/08\/Best-STL-Training-London_Excel-2010-Result.jpg\" alt=\"Training Excel London Results of using LEFT Function\" width=\"464\" height=\"300\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>The problem we have is that not all Supplier codes are 3 numbers in length.<br \/>\nWe can correct this by using another Excel function word, FIND.<\/p>\n<p>FIND function can return a number value of where a particular character appears, e.g. if we have a word \u201c<strong>BEST-STL TRAINING<\/strong>\u201d the FIND function would tell us that the hyphen is the 5<sup>th<\/sup> character.<\/p>\n<p>We could use this inside (nested) the LEFT function \u2013 But remember we do not want to include the hyphen (-) in our final result so we need to subtract 1.<\/p>\n<p>Example:<br \/>\n<a href=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/08\/Best-STL-Training-London_Excel-2010-Final.jpg\"><img decoding=\"async\" class=\"alignnone size-full wp-image-146\" src=\"https:\/\/www.stl-training.co.uk\/b\/exceltraining\/files\/2011\/08\/Best-STL-Training-London_Excel-2010-Final.jpg\" alt=\"Excel 2010 Training Learn to use FIND Function\" width=\"600\" height=\"297\" \/><\/a><\/p>\n<p>NOTE: Without subtracting 1 in the above example, it would return a value of 4<\/p>\n<p>Final Code: <strong>=LEFT(A2,FIND(\u201c-\u201c,A2)-1)<\/strong><\/p>\n<p><em>We could repeat this for the Item code by using a combination of RIGHT, LEN and FIND nested.<\/em><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In the following example there is a column of stock codes. The numbers represent our Supplier and the letters after the hyphen (-) indicates our Item number. Suppose we need to extract the Supplier and Item codes and place in separate cells. If our Supplier codes were all equal length e.g. 3 numbers long, we [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[28,171,220,333,337,353,377,465,512,541],"class_list":["post-142","post","type-post","status-publish","format-standard","hentry","category-excel-training","tag-2010-office","tag-excel","tag-excel-training-y","tag-left-function","tag-london","tag-microsoft-excel","tag-mm","tag-right-function","tag-spreadsheet-training","tag-training-in-london"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/142","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=142"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/142\/revisions"}],"predecessor-version":[{"id":6798,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/142\/revisions\/6798"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=142"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=142"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=142"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}