hey there this is Seth Ari tips.com and
in this video I'm gonna show you what to
do when you get a text file that looks
like this where it's just a complete
mess it's just data all over the place
and you want to get this into an excel
file and start organizing it and
separating into different columns but
how do you do that
when it's just this massive block of
text so I have to do is open up the text
file and if you're on a you know Windows
PC you can right click and you'll
probably see something that allows you
to select all the text and the entire
text file or if you're on a Mac like I
am right now you can do the exact same
thing just by putting your cursor right
up at the very top left of the text file
and holding down a command shift and
then the down arrow that's gonna
automatically select everything and then
I'm gonna right click copy and then
we're gonna go over here to Microsoft
Excel and right up here we're going to
highlight column a and I'm going to
click command V on a Mac and again if
you're using a Windows PC you would do
ctrl V to paste all of that data right
here yeah we're gonna go ahead and say
ok to this and we're not done yet but if
we want to take this information and
very neatly break it up based on where
all of the commas are inserted in here
it's really easy to do that so the way
this is currently set up is all of the
data here is living right here in column
a it's all stuck in this column so if we
want to parcel it out we would go up
here and click on this data and tab and
then click text to columns and we're
going to select delimited because in
this case we have the luxury of commas
or tabs separating each field if we did
not have commas we could try to go with
this fixed width option which would part
slot up based on where the spaces are
but I think in many many cases when you
have this situation you are gonna see
commas in there so go ahead and click
next and then we're going to uncheck tab
in check comma yeah and that's the old
no we're gonna have checked here and
we'll click next and just keep it here
on general and click finish it is gonna
take a second to compute because this is
a massive text file but as you can see
just like that it does split everything
up into us really nice and neat just the
way we would normally expect to see this
if we were downloading this kind of data
from a data service of some kind so if
you do run into a situation like that
where you get just a horrendously messy
text file like this
don't fret it's not the end of the world
Microsoft Excel has it covered so and
then from here you would just go ahead
and continue sorting the list the way
you normally would and also keep in mind
this is like sort of an extreme example
if you were ever seeing a list where say
there was a number of different names
I'll just create one here really quick
for this example so let's say you get a
list like this where the first and last
names are stuck in the same column but
you want to separate them so you could
just use the first name or just the last
name if you wanted to in some kind of a
mail merge or what have you so you could
do the exact same thing here this is
just a much simpler version of the
problem so we'd go ahead and select this
go back up here to text to columns
delimited comma general and finish there
you go that's the same thing so really
any time you're trying to separate out
data that's all stuck in the same column
as long as there's a comma between those
data points you should be alright and
also keep in mind if we just undo this
so let's just go ahead and get rid of
these commas to see how we would deal
with that so similarly now if we go over
here and hit text to columns and then
delimited now we can also check this
space options so even if there's not a
comma it will also do the same thing for
a space and then do it that way
you know I think the potential problem
you could see in this case is say if one
of these cells have like a full street
address in there like one two three
anywhere street and the other cells had
like a first and last name so in other
words if there's not a consistent number
of spaces and all the cell's you're
trying to separate then it's gonna kind
of mess it up because you're gonna have
inconsistencies there so I think the
comma option is obviously preferable
assuming you have the commas there but
if you don't you may be able to still
make it work or at least get you part of
the way there if you just have spaces
between that data and I got a thing like
99% of the time I don't know why you
wouldn't have one or the other and also
keep in mind if you happen to be using
Google sheets for this say for some
reason you don't have access to
Microsoft Excel it works very much the
same way so I just took that exact same
text file and pasted it right here into
column a of this Google sheet and
similarly you can go up here and see
data and I'm just gonna go down here and
click on split text to columns and the
thing I've noticed with Google sheets
I'm not sure why I probably because this
text file is so huge is it it goes a
little bit slower it needs a little more
time to process what's going on so just
be prepared for that if you're using the
freebie version of Google sheets but
also keep in mind that Google sheets is
another viable option so there we go
took I don't know how many seconds that
was but it finally happened and it does
work and I'm not seeing any issues in
there and actually find interesting
because I didn't have to tell it to
separate it based on commas or spaces it
just kind of did it anyway the point is
it works and that's another viable
alternative to excel in case you don't
have it thanks for watching and I'll see
you next time