This tutorial continues Calc Basics III now with LEFT, MID, and RIGHT formulas. By practicing this, you will be able to cut some text by number of characters to help you, for example, get first, middle and last names of students. Like usual, if you haven't followed this LibreOffice Calc series, read the first and second parts here. Let's start!

Subscribe to UbuntuBuzz Telegram Channel to get article updates.

 

Dataset

Like usual, firstly, type this dataset and save the document as calc-04-left-mid-right.ods for this exercise. Notice that the table spans across B2:C22 with twenty names of students. The NAME data spans from C3 to C22 from ABI SATYA DARMA to CLAUDIA LIDYAWATI.

After that, name the sheet with three names at once LEFT, MID, RIGHT like pictured below. Once this finished, you are ready to practice. 

 

LEFT

We want to take first names of all students:

1. Type the dataset above, if haven't done.

2. Put cursor at D3. 

3. Type the formula =LEFT(C3, 3)

4. As a result, you will get ABI, a 3 character name, the first name of ABI SATYA DARMA, calculated from the first character A.

5. Put cursor at D4, type the formula =LEFT(C4, 4) and you will get BUDI.

6. Put the cursor at D5, type the formula =LEFT(C5, 5) and you will get CINTA. 

7. Repeat step 2-6 to get first names of the rest of students from SILVIE (6 characters) to CLAUDIA (7 characters). You should find your formulas slightly different thanks to the differences of names.

MID

1. Put cursor at E3.

2. Type the formula =MID(C3, 5, 5)

3. As a result, you will get SATYA, 5 characters, the middle name of ABI SATYA DARMA, counted by 5 characters from S. 

4. Put cursor at E4, type the formula =MID(C4, 6, 3), and you get ONO.

5. Put cursor at E5, type the formula =MID(C5, 7, 7) and you get, blank space included, VIA EKA.

7. Repeat step 1-5 to get middle names (name right after the first name) of the rest of students from SILVIE WULAN to CLAUDIA LIDYAWATI.

    

RIGHT

1. Put cursor at F3. 

2. Type the formula =RIGHT(C3, 5)

3. As a result, you will get DARMA, a 5 character name, the last name of ABI SATYA DARMA, counted from the right.

4. Put it at F4, type the formula =RIGHT(C4, 6), you will get CANGGU.

5. Put it at F5, type the formula =RIGHT(C5, 5), you will get PUTRI. 

6. Please notice that not all names have their last name in this exercise, so just repeat 1-5 anyway for the rest of them, but give it 0 for names consisted of two words only so they will be empty cells. See picture below and full formulas at final result for more elaborate examples.

 

Final Result

As you completed the exercise, you should have the full calculation result of all columns like picture below.

To see all formulas instead of results, open menu View > Show Formula and you will see LibreOffice Calc displays all formulas like below.

Now you should have basic skills of the formulas LEFT, MID and RIGHT as shown above. Next time, you will learn about LEN, to count number of characters, and CONCATENATE, to join several text in different cells into one. See you next time!