This tutorial continues Calc Basics IV and now we will learn two new formulas namely LEN and CONCATENATE. As an addition, you will also learn new skills called Paste Special and also Compare Sheets to help you copying formula and text to your needs. As a reminder, if you haven't followed this LibreOffice Calc series, read the first and second parts here. Now let's study.

Subscribe to UbuntuBuzz Telegram Channel to get article updates.

Dataset

Once again, type this and save the document as calc-05-len-concatenate.ods for this exercise. Notice that the table spans across B2:C22 still with twenty student names. The name data spans across C3:C22 from ABI to CLAUDIA.

Then, name the sheet as LEN. You will create a separate CONCATENATE sheet later. Once created, you are ready to exercise. 

LEN

You will count number of characters out of every student name. 

1. Put cursor at D3. 

2. Type the formula =LEN(C3)

3. As a result, you will get 15 characters count from ABI SATYA DARMA with spaces included. 

4. Repeat step 1-3 for the rest of students.

CONCATENATE

This is the lesson, you will try Paste Special first, then work with CONCATENATE formula later. 

Creating dataset:

1. Open calc-04-left-mid-right.ods.

2. Open 'LEFT, MID, RIGHT' sheet. 

3. Select D2:F22. 

4. Copy. 

5. Open calc-5-len-concatenate.ods and create a new sheet named CONCATENATE. 

6. Put cursor at B2. 

7. Click menu Edit > Paste Special > Paste Unformatted Text > dataset created successfully in CONCATENATE sheet.

Doing the formula:

1. Put cursor at E3.

2. Type the formula =CONCATENATE(B3, " ", C3, " ", D3)

3. As a result, you will get full name ABI SATYA DARMA back from the contents of B3, C3, and D3 added altogether with spaces inbetween.

4. Put cursor at E4, type the formula =CONCATENATE(B4, " ", C4, " ", D4), and you will get BUDI ONO CANGGU.

5. Put cursor at E5, type the formula =CONCATENATE(B5, " ", C5, " ", D5) and you will get CINTA VIA EKA PUTRI.

6. Repeat 1-5 for the rest of student names.

7. Write FULL NAME as column title at E2.

Final Results

To view all formulas temporarily, click menu View > Show Formula. To compare two sheets at the same time, click menu Window > New and rearrange Calc windows side by side. See LibreOffice Forum.

To this point, you learned about how to use LEN and CONCATENATE formulas as well as smartly copying with Paste Special and smartly comparing with menu Window > New. Next time you will learn two new formulas namely ROUND and ROUNDDOWN. See you next time!

To be continued to part VI...