- [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 AutoSum.
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
that has everybody's usernames in there.
The username is everything in the email address
before the at symbol.
So you 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 at symbol.
Excel has this capabilities 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.
And now I'm going ahead to the data ribbon tab.
And here's this wonderful button called Text to Columns,
what it does.
And it will tell you what it does
if you hover your mouse over it.
Is that it's going to split one column of text,
and that's what we're working on, into multiple columns.
And you can specify the delimiter.
I'll click this button and you need to tell Excel,
what kind of data you're working with.
For example, is a delimited, or is it fixed width?
In this case it's delimited.
And our delimiter is the at symbol.
That's what's going to specify the before and after columns.
I'll click Next.
And it defaults to having the tab as the delimiter.
And you can see a preview of what the split
is going to look like in this screen below.
I'm going to uncheck tab, and I have lots of choices here.
These are common delimiters that you might find
in your Excel file,
like semi-colons and commas, even spaces,
which is great for name fields.
However, I'm going to place a check mark next to other
and put in the at symbol.
Now watch what happened to our data preview.
It successfully split off the email address into two pieces.
I'll click Next.
And it's asking us where we want the new data to go.
We do not want cell E1 is the destination
because there's already text in there.
Let's change it to G.
I'll click Finish.
And it started in column G, just like we asked it to.
Here's everything separated out.
In fact, we don't need this column at all.
I can delete the contents of column H.
And I can put a heading on this one, I'll call it username.
You can make it bold based to match.
And here's my lovely username field.
And the nice thing about this is that nothing is a formula.
It's all straight text.