Extract Part of Strings

There are several ways to extract part strings using formula. In this post I'll explain the use of "Left" & "Right" functions.

If you are wishing to download Excel file which has all examples, including this click here.

Left function gives the few characters in a string. Number of characters to show is based on your selection.
When you start entering "left" function you can see below text on excel

=Left(text,[num_chars])

Understanding above will make easy understanding the output. 

text means the mother string. We can use cell reference or String inside "" marks for this. (See examples below). num_chars denote the Number of characters to display. (Count from left)

Let cell B3 =AppleiPhone
Enter bellow formula to cell C3
=LEFT(B3,2)
The result will be "Ap" (2 characters from the left)

Enter below formula to cell D3
=LEFT("AppleiPhone",2)

See results.

The only difference in Right function is it counts from the right. To understand this enter bellow formula to cell E3
=RIGHT(B3,2)
The result will be "ne" (2 characters from the right)

Enter below formula to cell F3
=RIGHT("AppleiPhone",2)

Split Cells Using Formula

There are 2 methods to split the cell contents in excel data sheet. First one is using "mid" function and second one is using "Text to Columns" option in "Data Tools" sub group in "Data" tab. Here I'm going to explain the use of "mid" Function.

If you are wish to download excel file that contains the example, Please click here

Let's understand the function. The basic format of the function is as below

=mid(text,start_num, num_chars)

1. 'text' means the reference or text you want to split.
2. 'start_num' means count of the text you want to start the split. (Count from left) 
3. 'num_chars means' the count of the characters you want to extract from the original text.

As an example, assume that we want to extract "iPhone" from "AppleiPhone". Then 'start_num' is 6 and 'num_chars' is 6 because text count for "iPhone" is 6. You have to consider spaces also as a character when you are using this function. (See the differences between row 3 and 4 in the example file)

Round Your Data

Round is a function which allows you to round your data. There are 3 variations in the function.

1. Round - Round Data According to Standard Rules
2. Round Up - Round Up Your Data
3. Round Down - Round Down Your Data

You can download example file from here.

Round

The basic format of this is below.

=round(number,num_digits)

Number means the number you want to round. Num_digits refers to the number of decimal places in the final answer. Lets see how this works.

= round(23.41,0)

= 23

= round(5.56,1)

= 5.6

= round(12.33,2)

= 12.33

Round Up

The basic format of this function

=roundup(number,num_digits)

Meanings of number and num_digits are same as Round function. See how this works

= roundup(23.41,0)

= 24

= roundup(5.56,1)

= 5.6

= roundup(12.33,2)

= 12.33

Round Down

Round Down function is also works as above functions and see the variations.

Further, if you want to round in to tens' place just enter -1 as num_digits. See the equation below.

= round(23.41,-1)

= 20

Protect Your Data

Here I'm going to learn you how you can protect your data by unexpected / unauthorized editing. Go to this Link & download example file. (This file contains all the data for my previous lessons. This lesson can follow you without downloading the file)

Open any Microsoft Excel file.

Go to review tab and Changes sub group

Click on Protect Sheet option

Then select the options you need to do after the file is protected.

Set password and Click on Ok

Try to enter or delete any data.

You can realize that it is not allowed by Excel. If you want to unprotect the sheet using the "unprotect sheet" command.

If you need to enter or update data regularly unprotecting and protecting the sheet in regular intervals may be not suitable.

Then follow below steps.

Unprotect sheet if you have already protected it.

Click on Allow Users to edit ranges command on same sub group.

Click on "New" and select the range or cell you want to enter data.

Name it in the title field.

If you want to protect this cell / range also using a password, enter the password into the "Range Password' field. (If you entered a password you have to enter it to unlock your cell to be updated)

You can enter many ranges as you wish.

After adding the fields, click on apply and close the pop up window.

Then protect the sheet using previous steps.

Now try to edit ranges you assigned.