{"id":217428,"date":"2021-04-02T13:40:46","date_gmt":"2021-04-02T10:40:46","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/holy-sheet-how-to-split-full-names-into-first-and-last-with-google-sheets\/"},"modified":"2021-04-02T13:40:46","modified_gmt":"2021-04-02T10:40:46","slug":"holy-sheet-how-to-split-full-names-into-first-and-last-with-google-sheets","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/holy-sheet-how-to-split-full-names-into-first-and-last-with-google-sheets\/","title":{"rendered":"#Holy sheet: How to split full names into first and last with Google Sheets"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a27e22d9a137\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a27e22d9a137\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/holy-sheet-how-to-split-full-names-into-first-and-last-with-google-sheets\/#An_often_provided_solution\" >An often provided solution<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/holy-sheet-how-to-split-full-names-into-first-and-last-with-google-sheets\/#A_better_solution\" >A better solution<\/a><\/li><\/ul><\/nav><\/div>\n<p>&#8220;<strong>#Holy sheet: How to split full names into first and last with Google Sheets<\/strong>&#8221;<\/p>\n<div>\n                            <em>Welcome to TNW Basics, a collection of tips, guides, and advice on how to easily get the most out of your gadgets, <a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">app<\/a>s, and other stuff.<\/em><\/p>\n<p>Sometimes you\u2019ve gotta work with what you\u2019ve got\u00a0<span>\u2014<\/span> and that doesn\u2019t just apply to the intelligence and skills you\u2019ve respectively been given from nature or obtained from nurture. It can also apply to what has been referred to as the new oil: data. It\u2019s what runs through the veins of the new economy, replacing the industrial with the digital.<\/p>\n<p>And just as crude oil has\u00a0to be processed to become a usable product, raw data needs to be cleaned or transformed<span>\u2014 whether it\u2019s to make it readable for users or just to ship it off on to the next stage of data processing<\/span>.<\/p>\n<p>So let\u2019s zoom in on a common type of data, full names, and how we can separate them in bulk into first and last names. And we\u2019re doing that without using any fancy programming tool, just some simple formulas in good ol\u2019 spreadsheets.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"An_often_provided_solution\"><\/span>An often provided solution<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Usually full names consist of two parts: a first name and a last name.<\/p>\n<p>So the simple solution most people turn to, is find the space in the full name and then splitting the full name by that character\u2026 but this doesn\u2019t really work. Some full names might contain multiple spaces \u2014 so this commonly provided solution (looking at you internet) doesn\u2019t take longer names into account. Ergo, not good enough.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"A_better_solution\"><\/span>A better solution<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>So, how can we do this in a better way, accommodating all lengths of full names? Firstly, we have to decide what we consider the first name part, and what the last name part. For automation\/bulk purposes, let\u2019s go with the following: everything before the first space in the full name is the first name, and everything after the first space is the last name.<\/p>\n<p>This way, the full name \u2018Jose Domingo Sanchez\u2019 would be split into the first name \u2018Jose\u2019 and the last name \u2018Domingo Sanchez\u2019. Again, this would work for longer names as well.<\/p>\n<p>In Google Sheets, we can use the following formula to extract the first name:<\/p>\n<p><strong>=index(split(A2, \u201c \u201d), 1)<\/strong><\/p>\n<p>In cell B2 in our example <span style=\"color: #ffffff;\"><span style=\"background-color: #a4a3a3;\">below<\/span><\/span>, we refer to the text in cell A2 where the full name is written, to be split up by a space character. But we\u2019ve encapsulated this function with an index function, and point it to index number 1.<\/p>\n<p>This means that the full text won\u2019t be split among several columns based on the space character, but that we only extract the first word that\u2019s separated from the following with a space character, leaving Jose in B2.<\/p>\n<figure class=\"post-image post-mediaBleed aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1345288 lazy\" alt=\"\" width=\"1200\" height=\"438\" sizes=\"auto, (max-width: 1200px) 100vw, 1200px\" src=\"https:\/\/cdn0.tnwcdn.com\/wp-content\/blogs.dir\/1\/files\/2021\/04\/Screenshot-2021-04-02-at-11.55.21.png\" data-lazy=\"true\" srcset=\"https:\/\/cdn0.tnwcdn.com\/wp-content\/blogs.dir\/1\/files\/2021\/04\/Screenshot-2021-04-02-at-11.55.21.png 1200w, https:\/\/cdn0.tnwcdn.com\/wp-content\/blogs.dir\/1\/files\/2021\/04\/Screenshot-2021-04-02-at-11.55.21-280x102.png 280w, https:\/\/cdn0.tnwcdn.com\/wp-content\/blogs.dir\/1\/files\/2021\/04\/Screenshot-2021-04-02-at-11.55.21-540x197.png 540w, https:\/\/cdn0.tnwcdn.com\/wp-content\/blogs.dir\/1\/files\/2021\/04\/Screenshot-2021-04-02-at-11.55.21-270x99.png 270w, https:\/\/cdn0.tnwcdn.com\/wp-content\/blogs.dir\/1\/files\/2021\/04\/Screenshot-2021-04-02-at-11.55.21-796x291.png 796w\"\/><\/figure>\n<p>Next, we want the remainder of the full name to be inserted into cell C2.. and here\u2019s where the magic happens. Instead of using the split function, we can simply subtract the first name from the full name:<\/p>\n<p><strong>=trim(REGEXREPLACE(A2, B2, \u201c\u201d))<\/strong><\/p>\n<p>In cell C2 we use the regular expression replace formula (<span>REGEXREPLACE)<\/span> and refer to the starting text in A2 \u2014 then to the first name in B2 as the text which needs to be replaced in the starting text \u2014 and then\u00a0type an empty string (two double quotes without anything between it) to make sure the first name part in the full name gets replaced with nothing.<\/p>\n<p>The formula is encapsulated with a trim function so that we remove the space character that would precede the last name, because we subtracted just the first name from the full name, not the space after that.<\/p>\n<figure class=\"post-image post-mediaBleed aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-1345290 lazy\" alt=\"\" width=\"1238\" height=\"436\" sizes=\"auto, (max-width: 1238px) 100vw, 1238px\" src=\"https:\/\/cdn0.tnwcdn.com\/wp-content\/blogs.dir\/1\/files\/2021\/04\/Screenshot-2021-04-02-at-12.05.56.png\" data-lazy=\"true\" srcset=\"https:\/\/cdn0.tnwcdn.com\/wp-content\/blogs.dir\/1\/files\/2021\/04\/Screenshot-2021-04-02-at-12.05.56.png 1238w, https:\/\/cdn0.tnwcdn.com\/wp-content\/blogs.dir\/1\/files\/2021\/04\/Screenshot-2021-04-02-at-12.05.56-280x99.png 280w, https:\/\/cdn0.tnwcdn.com\/wp-content\/blogs.dir\/1\/files\/2021\/04\/Screenshot-2021-04-02-at-12.05.56-540x190.png 540w, https:\/\/cdn0.tnwcdn.com\/wp-content\/blogs.dir\/1\/files\/2021\/04\/Screenshot-2021-04-02-at-12.05.56-270x95.png 270w, https:\/\/cdn0.tnwcdn.com\/wp-content\/blogs.dir\/1\/files\/2021\/04\/Screenshot-2021-04-02-at-12.05.56-796x280.png 796w\"\/><\/figure>\n<p>So there you go, a solution to split full names into first and last names, no matter how long the names. Time to split some names yourself!<\/p>\n<\/p><\/div>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong>\n<\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/thenextweb.com\/basics\/2021\/04\/02\/holy-sheet-how-to-split-full-names-into-first-and-last-with-google-sheets\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;#Holy sheet: How to split full names into first and last with Google Sheets&#8221; Welcome to TNW Basics, a collection of tips, guides, and advice on how to easily get the most out of your gadgets, apps, and other stuff. Sometimes you\u2019ve gotta work with what you\u2019ve got\u00a0\u2014 and that doesn\u2019t just apply to the&#8230;<\/p>\n","protected":false},"author":1,"featured_media":217429,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/img-cdn.tnwcdn.com\/image\/tnw?filter_last=1&fit=1280,640&url=https:\/\/cdn0.tnwcdn.com\/wp-content\/blogs.dir\/1\/files\/2020\/03\/sheets_formula.png&signature=0a93b4a008c02bd156fb2e231ee6efc1","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[26293,100538,71612,75246],"class_list":["post-217428","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology","tag-google","tag-google-sheets","tag-insights","tag-spreadsheet"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/217428","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=217428"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/217428\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/217429"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=217428"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=217428"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=217428"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}