{"id":2987,"date":"2016-05-06T00:29:59","date_gmt":"2016-05-05T22:29:59","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=2987"},"modified":"2024-04-11T19:23:10","modified_gmt":"2024-04-11T17:23:10","slug":"split-first-last-name-using-excel-text-column","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/split-first-last-name-using-excel-text-column\/","title":{"rendered":"Split First &amp; Last Name Using Text to Columns"},"content":{"rendered":"<p>There are times when you receive a data set of employee full names in one column and you want to separate the full name into first name and surname in separate columns.<\/p>\n<p>One way is to use the <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/split-first-last-name-using-excel-power-query\/\" target=\"_blank\" rel=\"noopener noreferrer\">Power Query method<\/a>, which is great if you have lots of data that gets added each day, week or month and simply want to Refresh\u00a0your Query to get the output needed each time.<\/p>\n<p>If you want to quickly split a\u00a0cell&#8217;s full name into separate columns on an ad-hoc basis, then the <a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/excel-text-to-columns\/\" target=\"_blank\" rel=\"noopener\">Text to Columns<\/a> is the way to go.<\/p>\n<div class=\"after-post-box\">\n<p><iframe src=\"https:\/\/www.youtube.com\/embed\/isU1UXJrAh4?rel=0 \" width=\"800\" height=\"400\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><span data-mce-type=\"bookmark\" style=\"width: 0px;overflow: hidden;line-height: 0\" class=\"mce_SELRES_start\">\ufeff<\/span><\/iframe><a href=\"https:\/\/www.youtube.com\/c\/MyExcelOnlinecomtutorials?sub_confirmation=1\" target=\"_blank\" rel=\"noopener noreferrer nofollow\"><img loading=\"lazy\" decoding=\"async\" class=\"alignmiddle wp-image-111468 size-full\" style=\"vertical-align: middle\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2019\/04\/1.yt_.png\" alt=\"\" title=\"\"><\/a><\/p>\n<p class=\"line-two\"><a class=\"IRPP_minimalist icon-download button small\" href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/Text-to-Columns-Full-Names.xlsx\" target=\"_blank\" rel=\"noopener noreferrer\" data-leadbox=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/Text-to-Columns-Full-Names.xlsx\" data-leadbox-id=\"147284833f72a2:12f08263ab46dc\"><span class=\"ctaText px-3 text-capitalize\"> download excel workbook <i class=\"fa fa-download\"><\/i><\/span><span class=\"postTitle px-3\">Text-to-Columns-Full-Names.xlsx<\/span><\/a><\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 1:<\/strong> <\/span>Highlight your column&#8217;s data that has the full names<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/highlight-data.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Split First &amp; Last Name Using Text to Columns\" class=\"alignnone size-full wp-image-2988\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/highlight-data.jpg\" alt=\"Split First &amp; Last Name Using Text to Columns\" width=\"197\" height=\"317\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/highlight-data.jpg 197w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/highlight-data-186x300.jpg 186w\" sizes=\"(max-width: 197px) 100vw, 197px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 2:<\/strong><\/span> Go to <em><strong>Data &gt; Text to Columns<\/strong><\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/text-to-columns.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Split First &amp; Last Name Using Text to Columns\" class=\"alignnone size-full wp-image-2989\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/text-to-columns.jpg\" alt=\"Split First &amp; Last Name Using Text to Columns\" width=\"319\" height=\"127\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/text-to-columns.jpg 319w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/text-to-columns-300x119.jpg 300w\" sizes=\"(max-width: 319px) 100vw, 319px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 3:<\/strong><\/span> In Step 1 of 3 you need to select the <em><strong>Delimited<\/strong> <\/em>button and press <em><strong>Next<\/strong><\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/step-1.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Split First &amp; Last Name Using Text to Columns\" class=\"alignnone size-full wp-image-2990\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/step-1.jpg\" alt=\"Split First &amp; Last Name Using Text to Columns\" width=\"507\" height=\"408\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/step-1.jpg 507w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/step-1-300x241.jpg 300w\" sizes=\"(max-width: 507px) 100vw, 507px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 4:<\/strong><\/span> In Step 2 of 3, you need to &#8220;check&#8221; the <em><strong>Tab<\/strong> <\/em>and <em><strong>Space<\/strong> <\/em>boxes and press <em><strong>Next<\/strong><\/em><\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/step-2-1.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Split First &amp; Last Name Using Text to Columns\" class=\"alignnone size-full wp-image-2992\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/step-2-1.jpg\" alt=\"Split First &amp; Last Name Using Text to Columns\" width=\"507\" height=\"408\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/step-2-1.jpg 507w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/step-2-1-300x241.jpg 300w\" sizes=\"(max-width: 507px) 100vw, 507px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><span style=\"color: #ff0000\"><strong>STEP 5:<\/strong> <\/span>In Step 3 of 3 you need to\u00a0press the <em><strong>Finish<\/strong> <\/em>button<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/step-3.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Split First &amp; Last Name Using Text to Columns\" class=\"alignnone size-full wp-image-2993\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/step-3.jpg\" alt=\"Split First &amp; Last Name Using Text to Columns\" width=\"507\" height=\"408\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/step-3.jpg 507w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/step-3-300x241.jpg 300w\" sizes=\"(max-width: 507px) 100vw, 507px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p>This will split the column into two columns, separating the first from the second name. \u00a0You can go ahead and change the column headings to get the following result:<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/FINAL.jpg\" target=\"_blank\"><img loading=\"lazy\" decoding=\"async\" title=\"Split First &amp; Last Name Using Text to Columns\" class=\"alignnone size-full wp-image-2994\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/FINAL.jpg\" alt=\"Split First &amp; Last Name Using Text to Columns\" width=\"275\" height=\"317\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/FINAL.jpg 275w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/FINAL-260x300.jpg 260w\" sizes=\"(max-width: 275px) 100vw, 275px\" \/><\/a><\/p>\n<p>&nbsp;<\/p>\n<p><a href=\"https:\/\/www.myexcelonline.com\/meo-staging\/podcast\/009-excel-accountants-jeff-lenning-excel-podcast\/\" target=\"_blank\" rel=\"noopener noreferrer\"><img loading=\"lazy\" decoding=\"async\" title=\"Split First &amp; Last Name Using Text to Columns\" class=\"alignnone wp-image-2995 size-full\" src=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/Jeff_Lenning.jpg\" alt=\"Split First &amp; Last Name Using Text to Columns\" width=\"728\" height=\"90\" srcset=\"https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/Jeff_Lenning.jpg 728w, https:\/\/www.myexcelonline.com\/meo-staging\/wp-content\/uploads\/2016\/05\/Jeff_Lenning-300x37.jpg 300w\" sizes=\"(max-width: 728px) 100vw, 728px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>There are times when you receive a data set of employee full names in one column and you want to separate the full name into first name and surname in separate columns. One way is to use the Power Query method, which is great if you have lots of data that gets added each day, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":17617,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"Split First &amp; Last Name Using Text to Columns","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[6,41],"tags":[88,67],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/2987"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=2987"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/2987\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/17617"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=2987"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=2987"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=2987"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}