How to split first name and last name in Google Sheets

G'day guys, Chris Higgins from Electric Monk here. Hope you're doing well. So today I've got a really fantastic Google Sheets tip to share with you, and I'm only slightly embarrassed to admit that I have only just discovered this feature in the last few months. Now this is one of those features where if you're aware of it and you use it often, you'll likely think that this is the dumbest tutorial in history. However, if you're not already aware of this feature, then this video will probably ... maybe change your life.

So let's jump into Google Sheets and have a look at what's going on. You can see here I've got my sample list of contacts. These are all students at a particularly prestigious boarding school, and we've got a column for their name, their email address and their assigned school house.

The problem that we've got is that all of the students' names have been entered as full names in a single column rather than being split into separate columns. If you work with a lot of contact data, you will have received CSV files just like this yourself. And the issue is that platforms like HubSpot typically store names as two separate properties - first name and last name - and they won't actually allow you to upload any contact data if the names aren't formatted correctly. So, we need to find a way to split these names out into separate columns before we can upload this data into our CRM.

Now, the obvious answer is to go through them all line by line, cutting and pasting the surname values into a new column. And that's manageable if you've got a list like this that only contains 40 or so names, but if you're dealing with a file that includes hundreds or perhaps even thousands of names, that can be really time consuming.

So it turns out that there is a feature in Google Sheets that enables you to split data into multiple cells, and it makes it ridiculously quick to format a list of contacts just like this one. So the first thing we need to do is create a new empty column next to our full name column, which you can do by right clicking and selecting the Insert column option. It's worth eyeballing your list of names at this point to see if you've got any contacts with middle names or titles, because at the end of this process each name is going to be split into its own cell. So if you do have any contacts with three or four names, make sure you create a couple of extra columns to accommodate them. Once you've got your empty column in place, you need to highlight the column that contains the names that you want to split, then click Data, and then click Split text to columns.

You should then see this small tool bar that asks you what separator you'd like to use. Choose Space, and then that is literally it. Your names should now be split out into separate columns. The final thing you need to do is just ensure that your column headers are updated so that they'll be recognised when you import them into HubSpot.

I really hope you found this useful and please do feel free to let me know in the comments if there are any other MarTech tutorials that you'd like me to create. Cheers!