Cara menggunakan excel text functions

Have you ever needed to get the first and last name from a full name? Or maybe pull the street name from an address? 🤔

Tasks like these are easy!

But when you have hundreds or even thousands of lines of data, well…

…things become absolutely tedious! 😱

Lucky for us, Microsoft Excel provides the LEFT, RIGHT, and MID functions.

In this Excel tutorial, you learn to turn this…

These are the contact names and numbers you will be using in this tutorial!

…into this!

The above example is just a small demonstration of the important Excel functions you'll learn today!

You also learn how to unlock their full potential using LEN and FIND!

If you want to tag along, please download my sample data workbook here.

Table of Contents

Let’s dive right in!

Open practice workbook.

You’ll find the Contact Names and Contact Numbers of several people.

This could be a list of potential customers of a business. Or, a list of teachers’ contacts for students.

Information like this is usually split up into smaller bits for use in typical applications.

For example, you might need the Area Code for each contact.

To get this, you use the LEFT function.

Extract text string using the LEFT function

The LEFT function extracts a given number of characters starting from the left side of a string.

1. Type “=LEFT(” in cell C2 where you want the first Area Code.

Typing a function into a cell shows the function's tooltip

2. The first argument of the LEFT function is text, this is the source text string.

Typically, this is the cell reference and in this case, it’s B2.

So, type:

=LEFT(B2

You can click on a cell while typing a formula to set it as an argument

And put a comma to separate the next argument.

3. Next input is num_chars. This is the number of characters from the left of the string that will be extracted.

If you count the characters of the Area Code including the parentheses, you get 5.

So, input 5 and close the formula with a right parenthesis like this:

=LEFT(B2,5)

The last input is the number of characters to be extracted

4. Hit Enter.

Text string returned by the LEFT function

5. Let’s fill up the rest of the rows by double-clicking or dragging down the fill handle.

Use the fill handle to quickly copy formulas

Result:

The formula has successfully extracted the specified number of characters from the supplied text string

Great work!

Now, you need the Extension number and you can get this using the RIGHT function.

Extract text string using the RIGHT function

The RIGHT function extracts a given number of characters starting from the right side of a string.

It has the exact same arguments, text and num_chars, as the LEFT function.

1. You want the first Extension number to appear in cell D2, so, type “=RIGHT(“.

Pro Tip!

Excel automatically suggests formulas as you type.

For example, you can just type “=ri” and the suggestion for “=RIGHT(” will appear.

Press the Tab key to input the suggested formula.

Use this technique to quickly find a function in Excel

You can also hide Column C for now.

2. Next, input the first argument which is still cell B2.

=RIGHT(B2,

3. Then, count the number of characters from the right to get the Extension.

You get 3, so, your formula should be:

=RIGHT(B2,3)

The last input is the number of characters to be extracted

4. Hit Enter and fill in the rest of the rows using the fill handle again.

Result:

You've successfully extracted text from the end of a text

Alright!

But what if you need text from the middle part? Like the Landline number in this case.

For that, we have the MID function.

Extract text string using the MID function

The Excel MID function extracts a given number of characters starting from a specified position within the source string.

1. The first Landline number should appear in cell E2. So, type “=MID(“.

You can hide Column D.

The MID function in Excel

2. The MID function has the same first input as LEFT and RIGHT which is the source text.

So, the formula becomes:

=MID(B2,

3. The second input in the MID function is start_num.

This is the starting position from where the substring will be extracted.

Let’s count each character in the Contact Number:

The Landline number starts at position 7 and ends at position 14

Type this into your formula for the start_num:

=MID(B2,7,

3. The third input of the MID function is num_chars. This is the number of characters from start_num that will be included in the output substring.

From the character count shown above, you know that the last digit of the Landline is at position 14.

This means the whole Landline number consists of 8 characters.

So, your formula becomes:

=MID(B2,7,8)

Use of MID function example

4. Hit Enter and fill in the rest of the rows.

Result:

MID formula examples

Nicely done!

You now know how to use the LEFT, RIGHT, and MID functions!

These functions are often combined with other Excel functions to produce more complex formulas.

Let’s take a look at some examples below.

Extract substring before or after a specific character

Now, take a look at the Contact Names.

We can use LEFT to extract the Title used for each person.

However, since “Mr.”/”Ms.” and “Mrs.” have different string lengths, we have to input num_chars dynamically.

If you’ll notice, the period symbol “.” is common to all titles.

You can use this as the delimiter which indicates the string length needed.

It’s now just a matter of finding that delimiter in a given text! 🔎

Example 1: Using the FIND function

The FIND function returns the position of a specified text within another text string.

1. The first Title should appear in cell F2. So, type “=FIND(“.

You can hide Columns B to E for this example

2. The first input in FIND is find_text. This is the character or delimiter that you are looking for.

Since we are looking for the period symbol, type:

=FIND(“.”,

You can use any character or string in the FIND function

3. Next input is within_text and this is the source string.

In this case, it is cell A2. So, the above formula becomes:

=FIND(“.”, A2

Our formula will return the position of the first period character in cell A2

The third input is start_num. This is the starting position where Excel will look within the source string.

This input is optional and by default, FIND scans a string from the very beginning at the left.

Kasper Langmann, Microsoft Office Specialist

4. Hit Enter.

FIND returns 3 for the position of the period

The period is 3rd counted from the first character on the left

5. Now, use the above FIND function as the second input in the LEFT function.

Your formula becomes:

=LEFT(A2,FIND(“.”, A2))

This LEFT formula extracts all characters up to the first period

6. Hit Enter and fill in the other rows.

Result:

6. Hit Enter and fill in the other rows. Result:

The SEARCH function can also be used similarly. But it is not case-sensitive, unlike the FIND function.

Also, you can use any symbol in the FIND function as we’ll see in the next example.

Kasper Langmann, Microsoft Office Specialist

Example 3: Combining FIND functions

Next, you want to extract the First Name.

This is not as straightforward as the above examples. To do this, you first have to understand the concepts below.

Let’s get to it!

Let's count the characters in cell A2

You know that the First Name is between the first and second spaces ” ” which are in positions 4 and 10 respectively.

To get the First Name, you can use MID to extract characters starting from position 5 (character immediately after first space) up to position 9 (character preceding the second space).

But how exactly do you type the formula?

1. Getting the starting position for the MID function is easy enough.

You use FIND just as in the previous example. But instead of the period symbol, use the space character ” “.

Also, add 1 to the result. The starting position formula becomes:

=FIND(” “,A2)+1

Try this in cell G2.

The formula returned the integer value of 5

2. To get the position of the second space, you need to use the third input of the FIND function which is start_num.

You know that the position after the first space can be given by:

=FIND(” “,A2)+1

You can then use the above formula as the starting position of the second FIND function to find the second space like below:

=FIND(” “,A2, FIND(” “,A2)+1)

Try it out in cell G2.

The formula returned the integer value of 10

3. Now, you can combine the above formulas to express the string length input in the final MID function in cell G2.

Your MID formula is basically:

=MID(A2, position AFTER 1st space, position of 2nd space – position of 1st space – 1 )

So, the final formula in cell G2 will be:

=MID(A2,FIND(” “,A2)+1,FIND(” “,A2,FIND(” “,A2)+1)-FIND(” “,A2)-1)

You deduct 1 at the end to remove the extra space from the result.

Here we use Excel MID function again with FIND

4. Fill in the rows below and watch Excel magic happen!

Excel MID function extracts the string as specified by the FIND formulas

Incredible, isn’t it? 🤩

There’s one more interesting example below!

Example 3: Using the Excel LEN function

Finally, if you want to extract the Last Name, you use the RIGHT function.

Going back to the character count, you know that the Last Name is just the characters from the right of the Contact Name up to the second space.

In this case, the second space is at position 10

This length can be expressed as the length of the entire text minus the position of the second space.

You already know how to get the position of the second space from the previous example.

So, how do you get the length of the whole text string?

You can use the LEN function to get the length of any input string.

1. In cell H2, type the formula below:

=LEN(A2)

Hit Enter to get the length of the given text string

2. Now, you can combine this with the formula for the second space to get the length of the Last Name:

=LEN(A2) – FIND(” “,A2, FIND(” “,A2)+1)

This formula subtracts the position of the second space from the length of the original string

3. Finally, complete the RIGHT function by using the formula above as the number of characters to be extracted:

=RIGHT(A2,LEN(A2) – FIND(” “,A2, FIND(” “,A2)+1))

4. Hit Enter and fill in the rows below.

Result:

Now you have extracted the last names

Congratulations! 👏

You have completed the practice workbook!

Unhide all the rows and it should look like this

That’s it – Now what?

Now you’re well equipped to manipulate text strings using Microsoft Excel!

You can quickly extract data from thousands of records – not just contacts, but also addresses, lists, and so much more!

The formula examples we’ve shown you are just a few of the many applications you may encounter.

In fact, there are even more complex combinations of the above examples. You can combine other functions in Excel such as the SUBSTITUTE and REPT functions.

If you’d like to know more, click here to sign up for my free 30-minute email course.

Other resources

There are actually several ways to manipulate text strings in Microsoft Excel.

You can use other built-in features such as Flash Fill and Text to Columns. You can learn more by watching our YouTube video on how to Split Cells in Excel – 4 Methods [2022].

Bagaimana cara menggunakan function Excel?

Memasukkan function secara manual:.
Pilih sel yang akan berisi function. Dalam contoh kita, kita akan pilih sel C10..
Ketik tanda sama dengan (=), dan masukkan nama function yang diinginkan. ... .
Masukkan rentang sel untuk argumen di dalam tanda kurung. ... .
Tekan Enter pada keyboard Anda..

Apa fungsi dari formula teks pada excel?

Fungsi TEXT memungkinkan Anda untuk mengubah cara angka muncul dengan menerapkan pemformatan ke dalamnya dengan kode format. Hal ini bermanfaat ketika Anda ingin menampilkan angka dalam format yang lebih mudah dibaca, atau ketika ingin menggabungkan angka dengan teks atau simbol.

Bagaimana cara menggunakan wrap text pada excel?

Klik kanan sel yang ingin dibungkus teksnya kemudian klik Format Sel. Di tab Penyejajaran, centang kotak Bungkus Teks kemudian klik OK. Ya, teksnya dibungkus!

Apa fungsi dari wrap text?

Wrap text berarti menampilkan konten sel pada beberapa baris, dan bukan di satu baris panjang. Fungsi wrap text ini memungkinkan Anda untuk menghindari efek "kolom terpotong", sehingga membuat teks lebih mudah dibaca dan lebih baik untuk dicetak.