Formulas Collection Ms. Excel for Manipulating Data

The formula that I will show next is how to manipulate data. The context of data manipulation is not like the crime of changing data. However, how to combine sentences from separate words, determine the number of letters or numbers in a word, to determine the length of the name in a data. The formulas that will be introduced are: CONCATENATE, LEN, LEFT, MID, and RIGHT.

The data source for this project from Ignasius Ryan and I modified it.

• CONCATENATE. It is used to join separate words into a complete sentence. The formula: =CONCATENATE(select the first word, put quotation marks and spaces so that the sentences that are joined have a distance (“ “), choose the second word, put quotation marks and spaces so that the sentences that are joined have a distance (“ “), and so on).

In this project, I want to create an employee ID written by division, name, and year of birth.

Then the output will be as follows:

• LEN. Used to count the numbers or characters of data. In this project, the word or character that you want to count is the length of the name. Formula: =LEN(a word you want to count the number of words or characters).

Then the output will be as follows:

• LEFT. Used to remove the first few letters or numbers according to the desired or specified number. In this project, we want to issue the first 3 characters for each Population Identification Number (NIK). Formula: =LEFT(select a column, 3). The number 3 states that you want to get the first 3 numbers out.

Then the output is as follows:

• MID. Used to remove some letters or characters that are located in the middle. The number of letters or numbers located in the middle as desired. In this project, the number you want to issue starts with the 4th number and you want to issue 4 numbers on the NIK. Then the formula: =MID(choose a column, 4, 4).

• Then the output is as follows:

• RIGHT. Used to remove the last few letters or numbers according to the desired or specified number. In this project, we want to issue the last 2 characters for each Population Identification Number (NIK). Formula: =RIGHT(select a column, 2). The number 2 indicates that you want to get the last 2 numbers out.

Then the output is as follows: