1
00:00:00,05 --> 00:00:01,07
- [Instructor] Sometimes you may find

2
00:00:01,07 --> 00:00:02,08
that you think you're going to need

3
00:00:02,08 --> 00:00:04,06
to write a formula to do something,

4
00:00:04,06 --> 00:00:07,07
but as it turns out, Excel already has the capabilities

5
00:00:07,07 --> 00:00:10,08
to do that built in kind of like auto sum.

6
00:00:10,08 --> 00:00:13,07
But this time I'm going to show you an example that has

7
00:00:13,07 --> 00:00:16,02
to do with text, not numbers.

8
00:00:16,02 --> 00:00:19,04
Let's take this employee directory, it's pretty basic.

9
00:00:19,04 --> 00:00:23,04
It has a first name, last name, department, email address,

10
00:00:23,04 --> 00:00:25,04
and telephone extension.

11
00:00:25,04 --> 00:00:28,02
I want to put in a new column in G

12
00:00:28,02 --> 00:00:30,06
that has everybody's usernames.

13
00:00:30,06 --> 00:00:33,05
The username is everything in the email address

14
00:00:33,05 --> 00:00:35,05
before the at symbol.

15
00:00:35,05 --> 00:00:37,02
So you might think right off the bat

16
00:00:37,02 --> 00:00:39,03
that we would need some sort of formula

17
00:00:39,03 --> 00:00:41,06
to separate the text into two parts,

18
00:00:41,06 --> 00:00:45,04
one before the at symbol and one after the at symbol.

19
00:00:45,04 --> 00:00:49,06
Excel has this capability built right in.

20
00:00:49,06 --> 00:00:50,05
The first thing that we need

21
00:00:50,05 --> 00:00:52,08
to do is select the entire column,

22
00:00:52,08 --> 00:00:56,03
and we do that by clicking the column header.

23
00:00:56,03 --> 00:01:01,04
Now I'll go ahead and change to the Data ribbon tab,

24
00:01:01,04 --> 00:01:05,07
and here's this wonderful button called Text to Columns.

25
00:01:05,07 --> 00:01:08,01
In fact, if you hover your mouse over the button,

26
00:01:08,01 --> 00:01:11,00
it's going to tell you exactly what it does.

27
00:01:11,00 --> 00:01:13,04
It will split one column of text,

28
00:01:13,04 --> 00:01:15,02
and that's exactly what we're working on

29
00:01:15,02 --> 00:01:16,09
into multiple columns.

30
00:01:16,09 --> 00:01:19,09
And you can even specify the delimiter.

31
00:01:19,09 --> 00:01:23,03
I'll click this button and we first start by telling Excel

32
00:01:23,03 --> 00:01:25,08
what kind of data we're working with.

33
00:01:25,08 --> 00:01:29,01
For example, is it delimited or a fixed width?

34
00:01:29,01 --> 00:01:31,00
In this case it's delimited,

35
00:01:31,00 --> 00:01:33,08
and our delimiter is the at symbol.

36
00:01:33,08 --> 00:01:36,09
I'll click next.

37
00:01:36,09 --> 00:01:40,02
Now it defaults to having a tab as the delimiter,

38
00:01:40,02 --> 00:01:42,00
and you can see a preview of what it's going

39
00:01:42,00 --> 00:01:44,05
to look like in the screen below.

40
00:01:44,05 --> 00:01:47,00
Now you'll notice right now it's not delimited.

41
00:01:47,00 --> 00:01:50,00
That's because there's no tabs in that email address,

42
00:01:50,00 --> 00:01:53,00
so I'm going to go ahead and uncheck this.

43
00:01:53,00 --> 00:01:55,00
I do have lots of choices here.

44
00:01:55,00 --> 00:01:56,02
These are common delimiters

45
00:01:56,02 --> 00:01:59,01
that you might find in your Excel file, like semicolons

46
00:01:59,01 --> 00:02:02,02
and commas, even spaces which are great

47
00:02:02,02 --> 00:02:04,02
for separating name fields.

48
00:02:04,02 --> 00:02:07,09
However, I'm going to place a check mark next to other,

49
00:02:07,09 --> 00:02:11,00
and now I'll type in my at symbol.

50
00:02:11,00 --> 00:02:13,09
Now watch what happened to our preview.

51
00:02:13,09 --> 00:02:16,02
It successfully split off the email address

52
00:02:16,02 --> 00:02:18,05
into two pieces.

53
00:02:18,05 --> 00:02:21,00
I'll click next.

54
00:02:21,00 --> 00:02:24,06
It's asking us where we want the new data to go.

55
00:02:24,06 --> 00:02:27,04
We do not want cell E1 as the destination

56
00:02:27,04 --> 00:02:30,01
because there's already text here in this column.

57
00:02:30,01 --> 00:02:31,08
Let's change it to G,

58
00:02:31,08 --> 00:02:36,07
which is the next available empty column.

59
00:02:36,07 --> 00:02:43,00
I'll click finish

60
00:02:43,00 --> 00:02:46,01
and it started in column G, just like we asked it to.

61
00:02:46,01 --> 00:02:48,03
Here's everything separated out.

62
00:02:48,03 --> 00:02:51,00
In fact, I don't need this column at all.

63
00:02:51,00 --> 00:02:54,06
I can delete the contents of column H,

64
00:02:54,06 --> 00:02:57,00
and I can even put a heading in this one.

65
00:02:57,00 --> 00:03:01,01
I'll call it username.

66
00:03:01,01 --> 00:03:07,02
I can boldface it to match

67
00:03:07,02 --> 00:03:10,00
and here is my lovely username field.

68
00:03:10,00 --> 00:03:11,07
And the nice thing about this,

69
00:03:11,07 --> 00:03:13,06
if you look in the formula bar, you can see

70
00:03:13,06 --> 00:03:15,06
that nothing is a formula.

71
00:03:15,06 --> 00:03:18,00
It's all straight text.