- [Instructor] Sometimes you may find
that you think you're going to need
to write a formula to do something,
but as it turns out, Excel already has the capabilities
to do that built in kind of like auto sum.
But this time I'm going to show you an example that has
to do with text, not numbers.
Let's take this employee directory, it's pretty basic.
It has a first name, last name, department, email address,
and telephone extension.
I want to put in a new column in G
that has everybody's usernames.
The username is everything in the email address
before the at symbol.
So you might think right off the bat
that we would need some sort of formula
to separate the text into two parts,
one before the at symbol and one after the at symbol.
Excel has this capability built right in.
The first thing that we need
to do is select the entire column,
and we do that by clicking the column header.
Now I'll go ahead and change to the Data ribbon tab,
and here's this wonderful button called Text to Columns.
In fact, if you hover your mouse over the button,
it's going to tell you exactly what it does.
It will split one column of text,
and that's exactly what we're working on
into multiple columns.
And you can even specify the delimiter.
I'll click this button and we first start by telling Excel
what kind of data we're working with.
For example, is it delimited or a fixed width?
In this case it's delimited,
and our delimiter is the at symbol.
I'll click next.
Now it defaults to having a tab as the delimiter,
and you can see a preview of what it's going
to look like in the screen below.
Now you'll notice right now it's not delimited.
That's because there's no tabs in that email address,
so I'm going to go ahead and uncheck this.
I do have lots of choices here.
These are common delimiters
that you might find in your Excel file, like semicolons
and commas, even spaces which are great
for separating name fields.
However, I'm going to place a check mark next to other,
and now I'll type in my at symbol.
Now watch what happened to our preview.
It successfully split off the email address
into two pieces.
I'll click next.
It's asking us where we want the new data to go.
We do not want cell E1 as the destination
because there's already text here in this column.
Let's change it to G,
which is the next available empty column.
I'll click finish
and it started in column G, just like we asked it to.
Here's everything separated out.
In fact, I don't need this column at all.
I can delete the contents of column H,
and I can even put a heading in this one.
I'll call it username.
I can boldface it to match
and here is my lovely username field.
And the nice thing about this,
if you look in the formula bar, you can see
that nothing is a formula.
It's all straight text.