All language subtitles for 2. Text Formulas Case Study Solution

In this video we will use tax formulas to help families extract the information he needs from the list

of e-mail ideas.

Let us first insert two more columns that we need to process the e-mail ideas select the column B on

the Home tab in the sales group Clinton said Button and select Insert Sheet Columns.

We need two columns.

So let's do it once more.

We now have two additional columns in the spreadsheet.

Let us name the first column name lent.

We will use this column to restore the position of that that it symbol in the melody.

Let us call the second column extracted

name

in this column.

There is a store that name separated from their domain name to find a position of that that it symbol

will use the find function.

You can see the syntax of this command.

We first need to tell Excel what we are trying to find the tank to find that that symbol here.

And that takes where we are trying to find this symbol is this.

So let's enter a two as a reference to the text where we are trying to find the position of ad that

symbol.

Close the bracket and press center you can see 13 which is the position of at the right symbol.

In this e-mail idea now that we know where that their symbol is we can use another Excel function left

to try and get the melody up to one position.

Before that there is a symbol and that really was the name.

So let us do that let us select left in their function arguments dialog box.

We need to first enter that text that we are trying to get the information from.

That is the e-mail I feel.

Next we need to tell Excel how many characters we want to extract from this text string.

Let us enter B2 we tell the position of their symbol minus one.

The reason we are subtracting 1 from 13 is because 13 is a position of the act that it symbol.

We don't want that.

There is a symbol in the name.

So we are subtracting one from this click Okay.

And now you can see we have got the name extracted from the image lady.

We still need to find the first name and the last name.

So Mr. Summers can send personalized e-mails to his contact list.

The first name and the last name are separated by dot symbol.

So let's use that information to extract the first name.

Now in order to extract this information in one step instead of multiple steps that we followed in extracting

the phony name from the email lady they're going to combine two formulas together.

We'll use the left function to extend the first name from the extracted name C to sell as the expected

name.

Next we need to provide lift function the number of characters.

It needs to return from this cell C to.

So let's use the find.

Come on in conjunction with left to find the exact position of DOT in this extracted name

this find function will attend the exact position of dot but we want to return the first name which

ends one position before the dot symbol

gets dot.

Now we have the first name in order to get the last name will do a similar manipulation but will extract

the information from the right side of the extracted name string.

To do that let's use the right function

select the picks which is C2 from where you want to extract the information the right function.

If it does the information from the right hand side of the texting we need to provide the number of

characters that this function is to return from cell C2.

Again we don't know the exact position of DOT in this cell C2.

So let us use a combination of two functions.

What we're trying to do here is determine how long that texting in C2 cell is.

And from that lend or number of characters will subtract that position after dark symbol that will give

us the exact length of the last name in this field.

So Lindsey too will return the length of the field.

And from that we subtract the position of the dot symbol in this string.

You can see that we now have the last name extracted from the expected name field.

We can now combine the first name and the last name.

It definitely will values that gun get a net function which combines two text fields into one on the

formulas that under text.

Function select concatenate the first text that we want to string together as a first name.

We also want a space between first name and last name.

So in the second text to be concatenated input space the third text we want to concatenate is the last

name you select the last name click Okay.

And now we have the first name and last name strung together with this space in between.

That gives us the full name.

There's one more thing we need to do here.

Name in the list start with a capital letter and you can see that both the last name and the first name

is starting with the small letter.

So let's insert one more column which will have the name in the proper structure select column D on

the home tab in the same group click insert and click insert sheet columns.

Now I have one more column certain let's call it proper full name

will use the proper function to format the full name in this column on the formulas that link text select

proper in the text entry box select the cell F2 which has the string we want to format in proper text

a proper case select okay.

And now you can see that Excel has formatted the name in proper case with the first letter is capitalized.

So we have extracted all the information we wanted to for the first row in this table.

Let us copy all the formulas in the first row and paste in the rest of the rows to complete this data

extraction.

Now you can see we have got all the information that some research needs to do his job.