MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Split Data


April 28, 2017 - by Bill Jelen

Split Data

How to separate a column of Excel data in to two columns. How to parse data in Excel.


Watch Video

  • 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&" ","")

Auto-Generated Transcript

  • [Music]
  • Oh
  • [Applause]
  • hey welcome back it's time for another
  • dueling Excel podcast I'm Bill Jelenen
  • from mr. X I'll be joined by Mike Durham
  • from Excel Aslan this is our episode 182
  • split data from one cell to appear in
  • two cells alright today's question I
  • 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 one
  • point is romaine street he wants 123 in
  • one cell and main street in another cell
  • or Howard and Howard and then and 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 control shift
  • down arrow and then data text to columns
  • Texas columns in step 1 the data is
  • delimited it is delimited by US 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 two cell 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
  • spilled if I am in office 365 I'm going
  • to use text join text join that awesome
  • thing delimiter is of 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 but no one
  • has office 365 right so if you don't
  • have office 365 you have to do equal
  • this thing ampersand quote space quote
  • ampersand and that and then if there
  • were more quote space quote ampersand
  • 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 control
  • Elvie and then control-c LTS V to make
  • those B values and there we are
  • alright Mike let's see what you have
  • thanks MrExcel hey you loved me an
  • easy one here because you order you
  • mentioned ket and 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 okay 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 original data 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 drop-down we can they
  • use a space and look at this at the
  • left-most limiter when I click OK oh
  • there it is
  • now I'm going to name both of these
  • columns double-click part 1 enter double
  • click part two and enter now I can come
  • up here or close and load close and load
  • too and I can choose where to put this I
  • definitely want to dump it as a table
  • new worksheet existing worksheet I like
  • this click the collapse button I'm going
  • to say D 1 click OK then click load and
  • there we go our power query output
  • alright throw back to MrExcel 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 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 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 all right Mike back to you ding
  • ding ding that is the winner without a
  • doubt flash fill is the way to go here
  • notice we didn't have to convert it to a
  • table or open up any dialogue box just
  • typed a few examples and then ctrl e all
  • right well we could do it with formulas
  • even though flash fill would probably be
  • faster well look at this the pattern
  • just like lists Excel used over in flash
  • fill is everything before the first
  • space and then everything after so hey
  • I'm going to use the last function the
  • text is right there and how many
  • characters from the left well I'm going
  • to search for that space one two three
  • four using the search function fine text
  • space and double quotes comma within
  • that now notice that search would count
  • on its fingers one two three four and
  • that would get to that space that I want
  • that space so I subtract 1 close
  • parentheses control 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 I'm going to look through is the
  • full data comma the old text I want to
  • look for and then substitute nothing in
  • is almost one two three I actually want
  • to add
  • the space which I just took out in the
  • previous formula back in now it will
  • look for one two three space and then
  • Howard space and so on comma and the new
  • text I want to substitute in well to
  • tell substitute that you want to replace
  • it with nothing you say double quote
  • double quote no space in between close
  • parentheses and that will work
  • control-enter double click and send it
  • down alright throw it back to MrExcel
  • hey all right Mike both of your methods
  • were awesome let's do a quick wrap-up
  • here
  • my first method using text to columns
  • step one choose two limited steps you
  • choose a space and then click finish the
  • problem is that if you have multiple
  • spaces is going to end up in multiple
  • cells how to put those back together
  • office 365 text join or the old B to
  • ampersand quotes basically ampersand C 2
  • and so on Mike use power query it's
  • known as getting transforming Excel 2016
  • or in earlier versions 10 or 13 you
  • downloaded 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
  • chooses lemare's of space and then at
  • once at the leftmost delimiter I didn't
  • know you could rename a column by double
  • clicking I've been right-clicking and
  • renaming all the time and being a little
  • annoyed of that that will save me a lot
  • of time and I'm not closing load with
  • closing load too 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 of 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 vil maybe in 2010 you can power
  • query just adds a new columns over there
  • the left a 2 and then search look for
  • the space
  • subtract 1 to get rid of that space for
  • the second part substitute I was going
  • to use a 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 in the space and
  • replace it with nothing that was awesome
  • all right I want to thank everyone for
  • stopping by we'll see you next time for
  • another dueling excel podcast for mr.
  • excel in excel is fun

Download File

Download the sample file here: Duel182.xlsm

Title Photo: braetschit / Pixabay


Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.