Tom needs to split data. Bill & Mike discuss four alternatives.
Bill's first method using Text to Columns (found on the Data tab).
In step 1, choose delimited. In step 2, choose a space. Skip step 3 by clicking finish.
The text will split at each space, so anything with three words will end up in 3 cells. Put those back together with =TEXTJOIN(" ",True,B2:E2) or
with =B2&" "&C2&" "&D2
Mike's first method uses Power Query. Power Query is Get & Transform in 2016 or a free download for 2010 or 2013.
First, convert your data to a table using Ctrl+T. Then, in Power Query, from Table. Split Column, by Delimiter. Select Space and then at the left-most delimiter.
You can re-name a column by double-clicking!
Close & Load To… and choose a new spot on the worksheet.
Bill's second method is to use Flash Fill. Type new headings in A, B & C. Flash Fill will not work if you don't have headings! Type a pattern for the first two rows.
Go to the first blank cell in B and press Ctrl+e. Repeat for column C.
Mike's second method is to use these formulas:
For the first part, use =LEFT(A2,SEARCH(" ",A2)-1)
For the second part, use =SUBSTITUTE(A2,B2&" ","")
Bill's first method using Text to Columns (found on the Data tab).
In step 1, choose delimited. In step 2, choose a space. Skip step 3 by clicking finish.
The text will split at each space, so anything with three words will end up in 3 cells. Put those back together with =TEXTJOIN(" ",True,B2:E2) or
with =B2&" "&C2&" "&D2
Mike's first method uses Power Query. Power Query is Get & Transform in 2016 or a free download for 2010 or 2013.
First, convert your data to a table using Ctrl+T. Then, in Power Query, from Table. Split Column, by Delimiter. Select Space and then at the left-most delimiter.
You can re-name a column by double-clicking!
Close & Load To… and choose a new spot on the worksheet.
Bill's second method is to use Flash Fill. Type new headings in A, B & C. Flash Fill will not work if you don't have headings! Type a pattern for the first two rows.
Go to the first blank cell in B and press Ctrl+e. Repeat for column C.
Mike's second method is to use these formulas:
For the first part, use =LEFT(A2,SEARCH(" ",A2)-1)
For the second part, use =SUBSTITUTE(A2,B2&" ","")
Transcript of the video:
Bill Jelen: Hey, welcome back, it's time for another Dueling Excel Podcast. I'm Bill Jelen from MrExcel. I’ll be joined by Mike Girvin from Excel Is Fun. This is our Episode 182: Split Data from One Cell to Appear in Two Cells.
Alright, today's question is sent in by Tom.
Is there a way to easily split the data in one cell to make the data appear in two cells?
For example, 123 Main Street, he wants 123 in one cell and Main Street in another cell; or, Howard and Howard and then End. I have spent countless hours separating this kind of data. I'd appreciate hearing from your company while there are many, many different ways to do this.
The first thing I'm going to do is select all the Data, Ctrl+Shift+Down Arrow and then Data, Text to Columns. Text to Columns in Step 1, the data is Delimited. It is delimited by a Space and then just click Finish. Now, here's the hassle with this method is that if you have 123 Main Street it's going to end up in 3 cells instead of 2 cells. Oh, Power Query would make this so much easier but here we are. Alright, so what I'm going to do is I'm going to come out far to the right of the Data where I know that beyond where everything is built. If I am in Office 365, I'm going to use TEXTJOIN. TEXTJOIN, that awesome thing, delimiter is a Space.
Ignore empty cells True and then the cells that I want to concatenate together like that, and I just copy all of those down, Ctrl+V.
I will copy Ctrl+C and then Home, Paste, Paste as Values and at this point, I can delete these 3 extra columns.
Ahh, but no one has Office 365, right? So, if you don't have Office 365, you have to do = this thing &“ ”& that, and then if there were more “ ”& that, and if there were more, keep going. In this case it's pointless because there's nothing over in D but you get the idea. Ctrl+C, copy it down to the last row of data, Ctrl+V and then Ctrl+C , Alt+E S V to make those B values. And there we are, alright. Mike let's see what you have.
Mike Girvin: Thanks, MrExcel. Hey, you lobbed me an easy one here because you already mentioned Get & Transform Power Query, the old Text to Columns only allows you to say a space at every character, right? Well, if we use Power Query, we can use that Delimiter and say, “Hey, just split at the first occurrence.” Now, in order to get this Data into the Query Editor, we have to convert it to an Excel table. So I go up to Insert, Table or I use Ctrl+T. My table has headers, OK button is highlighted so I can click it with my mouse or just hit Enter. Now I want to name this Table so I'm going to come up here, OriginalData and Enter. Now, this is an Excel table, we can come up to Data and there it is From Table.
That'll bring it from Excel into the Editor.
The column is selected: Home Ribbon Tab, we can say Split Column by Delimiter or come over here and right-click, Split Column by Delimiter. From the dropdown, we can say, hey, use a Space and look at this At the left-most delimiter. When I click OK, BOOM! There it is. Now, I'm going to name both of these columns: double-click Part 1 Enter, double-click Part 2 and Enter. Now, I can come up here or Close & Load, Close & Load To and I can choose where to put this. I definitely want to dump it as a Table, New worksheet, Existing worksheet.
Highlight this, click the collapse button.
I'm going to say D1, click OK then click Load.
And there we go, our Power Query Output.
Alright, throw back to MrExcel.
Bill Jelen: Oh, Mike, Power Query is awesome!
Yeah, that's a great way to go. Here's another one that it might work if you have Excel 2013 or newer.
And what we're going to do is come out here and say First Part and then Second Part. Make sure to put these headings that if you don't put those headings, they don't have to be that but they have to have headings or it's not going to work. I'll put 123 and Main Street and then we'll put Howard and End, like that.
Now that we have a nice little pattern there, come out here in the Data Tab and Flash Fill which is Ctrl+E, press Ctrl+E right there and then press Ctrl+E right there. Beautiful thing is, we don't have to concatenate data together like in my example. Aright, Mike, back to you.
Mike Girvin: Ding-ding-ding. That is the winner without a doubt. Flash Fill is the way to go there. Notice, we didn't have to convert it to a table or open up any dialog box; just typed a few examples and then Ctrl+E.
Alright, well, we could do it with formulas even though Flash Fill would probably be faster.
Well look at this, the pattern just like this list cell used over in Flash Fill is everything before the first space and then everything after. So hey, I'm going to use the LEFT function, the Text is right there and how many characters from the left? Well, I'm going to search for that space – 1 2 3 4 using the SEARCH function, Find Text, space and “ ”, within that.
Now, notice that Search would count on its fingers 1 2 3 4 and that would get to that space that I want, that space so I -1)Ctrl+Enter, double-click and send it down. So, that always gets everything before the first space.
Now, notice we already have the text here so I can use the SUBSTITUTE function. The text that I'm going to look through is the Full Data, Comma, the Old Text I want to look for and then SUBSTITUTE. Nothing is almost 1 2 3. I actually want to add the Space which I just took out in the previous formula, back in. Now, it will look for 1 2 3, Space and then Howard, Space and so on, Comma and then the new text I want to substitute in. Well, to tell SUBSTITUTE that you want to replace it with nothing, you say “” no space in between, Close Parenthesis and that will work.
Ctrl+Enter, double-click and send it down.
Alright? Just throw it back to MrExcel.
Bill Jelen: Hey! Alright, Mike, both of your methods were awesome. Let's do a quick wrap-up here. My first method using Text to Columns: Step 1, choose Delimited; Step 2, choose a space, and then click Finish. The problem is that if you have multiple spaces it’s going to end up in multiple cells. I have to put those back together. Office 365 TEXTJOIN or the old B2&“ ”&C2 and so on.
Mike used Power Query, it's known as getting transforming Excel 2016 or in earlier versions 10 or 13, you download it and use the Power Query Tab. I even learned something here, but first you converted data using Ctrl+ T then from Table, Split Column, by Delimiter, choose Delimiter Space and then, at once, at the left-most delimiter. I didn't know you could rename a column by double- clicking.
I've been right-clicking and renaming all this time and being a little annoyed of that.
That will save me a lot of time. And then not Close & Load but Close & Load 2 and choose a new spot on the worksheet.
My second method was Flash Field. Now that is great if you have Excel 2013 or newer.
Just type the headings, it won't work without the headings. Type a pattern for the first two rows. Go to the first blank cell and press Ctrl+E in each column.
And then, Mike's method. Well, sure that was longer. It is a must if you have something before Excel 2013 because you can't use Flash Fill. Maybe in 2010 you can just Power Query, just add some new columns over there at the LEFT of A2 and then SEARCH, look for the space, and -1 to get rid of that space.
For the second part, SUBSTITUTE, I was going to use equal mid or something like that but this is even better because you already know what you want to take out. You want to take out B2 and the Space and replace it with nothing.
That was awesome.
Alright, I want to thank everyone for stopping by. We'll see you next time for another Dueling Excel Podcast from MrExcel and Excel is Fun.
Alright, today's question is sent in by Tom.
Is there a way to easily split the data in one cell to make the data appear in two cells?
For example, 123 Main Street, he wants 123 in one cell and Main Street in another cell; or, Howard and Howard and then End. I have spent countless hours separating this kind of data. I'd appreciate hearing from your company while there are many, many different ways to do this.
The first thing I'm going to do is select all the Data, Ctrl+Shift+Down Arrow and then Data, Text to Columns. Text to Columns in Step 1, the data is Delimited. It is delimited by a Space and then just click Finish. Now, here's the hassle with this method is that if you have 123 Main Street it's going to end up in 3 cells instead of 2 cells. Oh, Power Query would make this so much easier but here we are. Alright, so what I'm going to do is I'm going to come out far to the right of the Data where I know that beyond where everything is built. If I am in Office 365, I'm going to use TEXTJOIN. TEXTJOIN, that awesome thing, delimiter is a Space.
Ignore empty cells True and then the cells that I want to concatenate together like that, and I just copy all of those down, Ctrl+V.
I will copy Ctrl+C and then Home, Paste, Paste as Values and at this point, I can delete these 3 extra columns.
Ahh, but no one has Office 365, right? So, if you don't have Office 365, you have to do = this thing &“ ”& that, and then if there were more “ ”& that, and if there were more, keep going. In this case it's pointless because there's nothing over in D but you get the idea. Ctrl+C, copy it down to the last row of data, Ctrl+V and then Ctrl+C , Alt+E S V to make those B values. And there we are, alright. Mike let's see what you have.
Mike Girvin: Thanks, MrExcel. Hey, you lobbed me an easy one here because you already mentioned Get & Transform Power Query, the old Text to Columns only allows you to say a space at every character, right? Well, if we use Power Query, we can use that Delimiter and say, “Hey, just split at the first occurrence.” Now, in order to get this Data into the Query Editor, we have to convert it to an Excel table. So I go up to Insert, Table or I use Ctrl+T. My table has headers, OK button is highlighted so I can click it with my mouse or just hit Enter. Now I want to name this Table so I'm going to come up here, OriginalData and Enter. Now, this is an Excel table, we can come up to Data and there it is From Table.
That'll bring it from Excel into the Editor.
The column is selected: Home Ribbon Tab, we can say Split Column by Delimiter or come over here and right-click, Split Column by Delimiter. From the dropdown, we can say, hey, use a Space and look at this At the left-most delimiter. When I click OK, BOOM! There it is. Now, I'm going to name both of these columns: double-click Part 1 Enter, double-click Part 2 and Enter. Now, I can come up here or Close & Load, Close & Load To and I can choose where to put this. I definitely want to dump it as a Table, New worksheet, Existing worksheet.
Highlight this, click the collapse button.
I'm going to say D1, click OK then click Load.
And there we go, our Power Query Output.
Alright, throw back to MrExcel.
Bill Jelen: Oh, Mike, Power Query is awesome!
Yeah, that's a great way to go. Here's another one that it might work if you have Excel 2013 or newer.
And what we're going to do is come out here and say First Part and then Second Part. Make sure to put these headings that if you don't put those headings, they don't have to be that but they have to have headings or it's not going to work. I'll put 123 and Main Street and then we'll put Howard and End, like that.
Now that we have a nice little pattern there, come out here in the Data Tab and Flash Fill which is Ctrl+E, press Ctrl+E right there and then press Ctrl+E right there. Beautiful thing is, we don't have to concatenate data together like in my example. Aright, Mike, back to you.
Mike Girvin: Ding-ding-ding. That is the winner without a doubt. Flash Fill is the way to go there. Notice, we didn't have to convert it to a table or open up any dialog box; just typed a few examples and then Ctrl+E.
Alright, well, we could do it with formulas even though Flash Fill would probably be faster.
Well look at this, the pattern just like this list cell used over in Flash Fill is everything before the first space and then everything after. So hey, I'm going to use the LEFT function, the Text is right there and how many characters from the left? Well, I'm going to search for that space – 1 2 3 4 using the SEARCH function, Find Text, space and “ ”, within that.
Now, notice that Search would count on its fingers 1 2 3 4 and that would get to that space that I want, that space so I -1)Ctrl+Enter, double-click and send it down. So, that always gets everything before the first space.
Now, notice we already have the text here so I can use the SUBSTITUTE function. The text that I'm going to look through is the Full Data, Comma, the Old Text I want to look for and then SUBSTITUTE. Nothing is almost 1 2 3. I actually want to add the Space which I just took out in the previous formula, back in. Now, it will look for 1 2 3, Space and then Howard, Space and so on, Comma and then the new text I want to substitute in. Well, to tell SUBSTITUTE that you want to replace it with nothing, you say “” no space in between, Close Parenthesis and that will work.
Ctrl+Enter, double-click and send it down.
Alright? Just throw it back to MrExcel.
Bill Jelen: Hey! Alright, Mike, both of your methods were awesome. Let's do a quick wrap-up here. My first method using Text to Columns: Step 1, choose Delimited; Step 2, choose a space, and then click Finish. The problem is that if you have multiple spaces it’s going to end up in multiple cells. I have to put those back together. Office 365 TEXTJOIN or the old B2&“ ”&C2 and so on.
Mike used Power Query, it's known as getting transforming Excel 2016 or in earlier versions 10 or 13, you download it and use the Power Query Tab. I even learned something here, but first you converted data using Ctrl+ T then from Table, Split Column, by Delimiter, choose Delimiter Space and then, at once, at the left-most delimiter. I didn't know you could rename a column by double- clicking.
I've been right-clicking and renaming all this time and being a little annoyed of that.
That will save me a lot of time. And then not Close & Load but Close & Load 2 and choose a new spot on the worksheet.
My second method was Flash Field. Now that is great if you have Excel 2013 or newer.
Just type the headings, it won't work without the headings. Type a pattern for the first two rows. Go to the first blank cell and press Ctrl+E in each column.
And then, Mike's method. Well, sure that was longer. It is a must if you have something before Excel 2013 because you can't use Flash Fill. Maybe in 2010 you can just Power Query, just add some new columns over there at the LEFT of A2 and then SEARCH, look for the space, and -1 to get rid of that space.
For the second part, SUBSTITUTE, I was going to use equal mid or something like that but this is even better because you already know what you want to take out. You want to take out B2 and the Space and replace it with nothing.
That was awesome.
Alright, I want to thank everyone for stopping by. We'll see you next time for another Dueling Excel Podcast from MrExcel and Excel is Fun.
