Trending February 2024 # How To Use Vba Worksheet Function In Excel? # Suggested March 2024 # Top 6 Popular

You are reading the article How To Use Vba Worksheet Function In Excel? updated in February 2024 on the website Eastwest.edu.vn. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested March 2024 How To Use Vba Worksheet Function In Excel?

Worksheets in Excel VBA

Worksheets in excel are the sheets that have rows and columns in which we work. Whenever we open excel, we work on them. Now when working in excel, we use functions that are called worksheet functions, and we can use similar functions in VBA. In this article, we will learn about various functions in VBA to work on the worksheet. The most important thing to remember is while working in VBA; we have to always use the worksheet function as the data in excel is stored in the worksheet itself.

A worksheet is basically an object in VBA. Now there are two different methods of using this object. One is by referring to the Worksheets method, and another is by referring to the Sheets method. Since this article is about worksheets, we will move further with the worksheets object. There are different objects to use a worksheet object. The proper way to use this object is explained below,

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Worksheets( “ Sheet Name “ ). Methods

Now we need to remember all the different methods out there to use them in our code. We will capture some of the important and daily use of them by a few examples.

How to Use VBA Worksheets in Excel?

We will learn how to use a VBA Worksheets Function with few examples in excel.

You can download this VBA Worksheet Excel Template here – VBA Worksheet Excel Template

Example #1 – VBA Worksheets

First, let us try to select a worksheet using the worksheet object in VBA.

Step 1: Before we move ahead, we need to open VB Editor from the developer’s tab as shown in the image below; it is available in the code’s section

Step 3: Now define a subprocedure as shown below,

Code:

Sub

Example1()

End Sub

Step 4: Since we can see that we have three different worksheets in our workbook as sheet 1, sheet 2 and sheet 3. Let us select sheet 2 for this example,

Step 5: The method to select sheet 2 using the worksheet object is as follows,

Code:

Sub

Example1() Worksheets("Sheet2").Select

End Sub

Select the method we are using for this object.

Step 6: Execute the above code and see the result in the workbook as follows,

We can see that sheet 2 has been selected.

Example #2 – VBA Worksheets

Now we have sheet 2 selected, but we want to put data in cell A1 of sheet 1. To do this, we can either select sheet 1 as shown above and then use the command to put a random value in the active sheet, which is sheet 1. But that is twice the work. We can use the Activate method and put a random value.

Step 1: In the same module, define another subprocedure for our next example as follows,

Code:

Sub

Example2()

End Sub

Step 2: First, let us activate sheet 1 using the worksheet object as follows,

Code:

Sub

Example2() Worksheets("sheet1").Activate

End Sub

Step 3: Now, we will use the range property to put a random value in cell A1 as shown below,

Code:

Sub

Example2() Worksheets("sheet1").Activate Range("A1").Value = 10

End Sub

Step 4: Execute the above code and see the result in sheet 1 as follows,

Since our selection was sheet 2 in the first example, This method not only selected sheet 1 for us but also we were able to put a value in cell A1.

Example #3 – VBA Worksheets

Now let us try to rename a worksheet name using the worksheet object. We will rename sheet 3 to Sheet Third.

Code:

Sub

Example3()

End Sub

Step 2: In order to change the worksheet 3 name, we need to use the .Name method as follows,

Code:

Sub

Example3() Worksheets("Sheet3").Name = "Sheet Third"

End Sub

Step 3: Now, if we can see the original name of the sheet before this code, as shown below,

Step 4: Execute the above code and see the result in the workbook as follows,

Example #4 – VBA Worksheets

Now in this method, first, we will add another worksheet manually using the + sign in the workbook and delete it using the worksheet object in VBA.

Step 1: First, let us create another worksheet in the workbook as shown below,

Step 2: We can see that now there is another sheet as sheet 4. Now define subprocedure for our fourth example.

Code:

Sub

Example4()

End Sub

Step 3: To delete the worksheet from the workbook, we will use the .Delete method to worksheet object as shown below,

Code:

Sub

Example4() Worksheets("Sheet4").Delete

End Sub

Example #5 – VBA Worksheets

Now let us use another cool feature of this object. In this example, we will count the number of worksheets present in our workbook and display them.

Step 1: In the same module, define another subprocedure as follows,

Code:

Sub

Example5()

End Sub

Step 2: Declare a variable as an integer as follows,

Code:

Sub

Example5()

Dim

COunt

As Integer

End Sub

Step 3: Now in Count store the value of the number of worksheets in the workbook using the count method as shown below,

Code:

Sub

Example5()

Dim

COunt

As Integer

COunt = Worksheets.COunt

End Sub

Step 4: Display the value stored in the variable using the msgbox function as follows,

Code:

Sub

Example5()

Dim

COunt

As Integer

COunt = Worksheets.COunt MsgBox COunt

End Sub

Step 5: Execute the above code to get the count of worksheets in our workbook as follows,

Things to Remember

A worksheet is an object in VBA.

We use different methods to refer to different worksheets in VBA.

If we enter the incorrect name of the sheets, we will encounter an error.

The argument in the worksheet object for sheet name is treated as a string.

Recommended Articles

This is a guide to VBA Worksheets. Here we discuss how to use the Excel VBA Worksheets function along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

You're reading How To Use Vba Worksheet Function In Excel?

How To Use Excel Vba Find Function?

Excel VBA Find Function

Who doesn’t know FIND method in excel? I am sure everybody knows who are dealing with excel worksheets. FIND or popular shortcut key Ctrl + F will find the word or content you are searching for in the entire worksheet as well as in the entire workbook. When you say find means you are finding in cells or ranges isn’t it? Yes, the correct find method is part of the cells or ranges in excel as well as in VBA.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Similarly, in VBA Find, we have an option called FIND function which can help us find the value we are searching for. In this article, I will take you through the methodology of FIND in VBA.

Formula to Find Function in Excel VBA

In regular excel worksheet, we simply type shortcut key Ctrl + F to find the contents. But in VBA we need to write a function to find the content we are looking for. Ok, let’s look at the FIND syntax then.

I know what is going on in your mind, you are lost by looking at this syntax and you are understanding nothing. But nothing to worry before I explain you the syntax let me introduce you to the regular search box.

If you observe what is there in regular Ctrl + F, everything is there in VBA Find syntax as well. Now take a look at what each word in syntax says about.

What: Simply what you are searching for. Here we need to mention the content we are searching for.

After: After which cell you want to search for.

LookIn: Where to look for the thing you are searching For example Formulas, Values, or Comments. Parameters are xlFormulas, xlValues, xlComments.

LookAt: Whether you are searching for the whole content or only the part of the content. Parameters are xlWhole, xlPart.

SearchOrder: Are you looking in rows or Columns. xlByRows or xlByColumns.

SearchDirection: Are you looking at the next cell or previous cell. xlNext, xlPrevious.

MatchCase: The content you are searching for is case sensitive or not. True or False.

MatchByte: This is only for double-byte languages. True or False.

SearchFormat: Are you searching by formatting. If you are searching for format then you need to use Application.FindFormat method.

This is the explanation of the syntax of the VBA FIND method. Apart from the first parameter, everything is optional. In the examples section, we will see how to use this FIND method in VBA coding.

How to Use Excel VBA Find Function?

We will learn how to use a VBA Find Excel function with few examples.

You can download this VBA Find Excel Template here – VBA Find Excel Template

VBA Find Function – Example #1

First up let me explain you a simple example of using FIND property and find the content we are looking for. Assume below is the data you have in your excel sheet.

Step 1: From this, I want to find the name John, let’s open a Visual basic and start the coding.

Code:

Sub

Find_Ex1()

End Sub

Step 2: Here you cannot start the word FIND, because FIND is part of RANGE property. So, firstly we need to mention where we are looking i.e. Range.

Step 3: So first mention the range where we are looking for. In our example, our range is from B2 to B11.

Code:

Sub

Find_Ex1() Range ("B2:B11")

End Sub

Step 5: Select the FIND property and open the bracket.

Step 6: Our first argument is what we are searching for. In order to highlight the argument we can pass the argument like this What:=, this would be helpful to identify which parameter we are referring to.

Code:

Sub

Find_Ex1() Range ("B2:B11").Find(What:="John")

End Sub

Step 7: The final part is after finding the word what we want to do. We need to select the word, so pass the argument as .Select.

Code:

Sub

Find_Ex1() Range("B2:B11").Find(What:="John").Select

End Sub

Step 8: Then run this code using F5 key or manually as shown in the figure, so it would select the first found word Johnson which contains a word, John.

VBA Find Function – Example #2

Code:

Sub

Find_Ex2() Range("D2:D11").Find(

End Sub

Step 2: In the WHAT argument type the word “No Commission”.

Code:

Sub

Find_Ex2() Range("D2:D11").Find(What:="No Commission",

End Sub

Code:

Sub

Find_Ex2() Range("D2:D11").Find(What:="No Commission", LookIn:=xlComments).Select

End Sub

Deal with Error Values in Excel VBA Find

If the word we are searching for does not find in the range we have supplied VBA code which will return an error like this.

In order to show the user that the value you are searching for is not available, we need the below code.

If the above code found value then it shows the value & cell address or else it will show the message as “The Value you are searching for is not available in the supplied range!!!”.

Things to Remember

VBA FIND is part of the RANGE property & you need to use the FIND after selecting the range only.

In FIND first parameter is mandatory (What) apart from this everything else is optional.

If you to find the value after specific cell then you can mention the cell in the After parameter of the Find syntax.

Recommended Articles

This has been a guide to VBA Find Function. Here we discussed VBA Find and how to use Excel VBA Find Function along with some practical examples and downloadable excel template. You can also go through our other suggested articles –

How To Use Offset Function In Excel?

Excel OFFSET Formula (Table of Contents)

What is OFFSET Formula in Excel?

OFFSET Formula in Excel gives a cell or a range of dynamic rectangular cells as an output, a subset of the total given range with a specified number of rows, columns, height, and width.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Syntax of OFFSET Formula in Excel

Explanation of OFFSET Function in Excel:

reference: The starting point for OFFSET, or we can say a range of cells that can be considered the OFFSET base.

rows: The number of rows to OFFSET below or above the base reference.

cols: The number of columns to OFFSET to the right or left of the base reference.

height: Number of rows in returned reference.

width: Number of columns in returned reference.

The height and width are the optional parameters for this function and can be used to determine the size of the reference created.

How to Use OFFSET formula in Excel?

We will now learn how to write the offset formula to get the lookup value. Let’s understand the offset formula with some examples in Excel.

You can download this OFFSET Formula Excel Template here – OFFSET Formula Excel Template

Excel OFFSET Formula – Example #1

Suppose we have data as given below.

In cell E2, input the following formula:

=OFFSET(A1, 11, 1)

Once done with the formula, press Enter and see the output.

What this formula did is it has taken reference from cell A1 (Year) and moved down by 11 rows after A1 (i.e. row no. 12) and then gained the value which is coordinate of the 11th row after A1 and first column (column A = 0 column B = 1 in this case. As we know, we can go both right and left), which is $ 141.27 Million.

Excel OFFSET Formula – Example #2

Now we will see another example of a reference cell for a specific year. Put the following formula in cell E3:

=OFFSET(A1, 11, 0)

Once done with the formula, press Enter and see the output.

If you have seen the formula, it is the same as of previous; the only change is in column reference. The column reference 0 (Zero) means the value will be captured from column A for the 11th row after A1 (which is 2010).

Excel OFFSET Formula – Example #3

=SUM(OFFSET(A1,COUNT(A:A), 0, -N))

To sum up the last N observations from your data, you can use the SUM function along with the OFFSET and COUNT function together. In this example, we will sum up the last 5 data points. Please note that when we say N, it’s a generalization of any integer value. So, you can calculate for the last 3, last 4, last 5, etc., depending on your dataset as well as your requirement.

Let’s have it done for our data set:

Input the following formula in cell E2 of the excel file.

=SUM(OFFSET(B1,COUNT(B:B), 0, -5))

As we have already seen, the Excel OFFSET function outputs rectangular dynamic ranges when given a starting reference.

In this case, the starting reference is provided in terms of B1 (as a first argument to the OFFSET function).

We used the COUNT function because we need the sum of observations from the last value. COUNT Function helps OFFSET to determine how many observations (rows) are there in column B. It counts all the numeric values in column B (19 is the count).

With a count of 19, the OFFSET formula becomes:

=OFFSET(B1, 19, 0, -5)

This formula starts Offsetting at B1 with 19 rows and the same column (0). Then height parameter -5 helps this function to extend the range into a backward direction by 5 rows (see the negative sign associated with 5).

The dynamic range of the Last five observations is given as an input to the sum function, which yields in summing those from column B.

Excel OFFSET Formula – Example #4

In this example, we will calculate the Median Sales for the Last 3 Years. OFFSET function can be used as an argument to calculate the same.

Put the following formula in cell E2:

Press Enter to see the output.

This formula works on lines similar to the previous one.

OFFSET Creates a dynamic range of the last three observations from column B with the help of the COUNT function (=OFFSET(A1, 19, 0, -3)).

This can be given as an output to the MEDIAN function. Which then sorts the observations in either ascending or descending order and then selects the middlemost value as you see in output ($ 212.07).

Excel OFFSET Formula – Example #5

Here we will calculate the median year using MEDIAN and OFFSET Function.

Use the following formula for the same in cell E3:

=MEDIAN(OFFSET(A1,COUNT(A:A),0,-3))

The same thing has happened here. The formula took up a dynamic range created by the OFFSET function with the help of COUNT. And used it as an argument to a MEDIAN function which then sorted the year values in ascending or descending order and then given output as 2023.

Usage of the OFFSET formula is in creating the dynamic ranges. It’s always better to work on dynamic ranges instead of static ones, as you might be having data that gets added every now and then. For example, Employee Master. Your Employee Master Data will always be getting increased every now and then as some new employees join. Every time using the same formula does not make any sense. Instead of that, create a range that is dynamic using the OFFSET function and see the magic. It will certainly save you time.

Things to Remember

OFFSET function only returns a reference to a particular range of cells in Excel. It does not move the cells from their original positions.

By default, this function works on the upper part (in terms of rows) and right-hand side (in terms of columns). However, it can be provided with negative arguments to reverse the default functionality. A negative row argument helps offset the data from down to up, and a negative col argument helps offset the data to the left.

OFFSET formula may slow down the Excel functionality as it is a formula that gets recalculated every time there is a change in the worksheet.

#REF error will be thrown if your range is outside the range of your workbook.

Any other function which expects a reference is able to use the OFFSET function within it.

Recommended Articles

This is a guide to the OFFSET Formula in Excel. Here we discuss how to use OFFSET Function in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

How To Use Normsinv Formula In Excel?

Excel NORMSINV (Table of Contents)

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

Introduction to Excel NORMSINV

The NORMSINV function in Excel calculates the probability of inverse normal cumulative distribution, which has a mean and standard deviation. Normsinv function can be seen as chúng tôi To find NORMSINV, first, we need to calculate the Normal Distribution; we must have X, Mean, and Standard Deviation. Once we get the value of Normal Distribution, we can easily calculate NORMSINV using the probability we got as per syntax.

Syntax of Excel NORMSINV

Argument:

Probability – Which is nothing but probability corresponds to the normal distribution.

How to Use NORMSINV Formula in Excel?

Microsoft Excel categorizes the NORMSINV built-in function under the statistical function. This is illustrated in the screenshot below, which calculates the inverse of the normal cumulative distribution for a given probability.

Go to Formulas Menu.

Choose a Statistical category under that we will find the NORM. The DIST function is shown below.

Example #1 – Using chúng tôi and NORMSINV

To use chúng tôi function, let’s start with an easy example where we need to find out the Student’s Grades; suppose we have the class exam with an average grade of 70, i.e., mu=70 and class standard deviation is 3 Points, i.e., sigma=3 here we need to find out what is the probability that students got the marks 73 or below, i.e., P(X<=73). So let’s see how to find out the probability using the chúng tôi function.

X=3

Mean=70

Standard Deviation=3

Apply the chúng tôi function as below.

Suppose we apply the above chúng tôi function, we will get the probability of 0.0807.

Now apply the NORMSINV function to find the inverse of the normal cumulative distribution, as shown below.

Result –

In the below result, we can see that we got negative values -1.40067 for the given probability, i.e., the inverse of normal cumulative distribution.

Example #2 – Mean and Exact Standard Deviation

Let’s see another example with curve-based data to get to know the mean and exact standard deviation.

Mean =7

Standard Deviation=1.3

Standard Deviation Increment as -3

To get the bell curve, we have to add a 0.1 to standard deviation increment where the data is shown below.

After applying the formula, the result is shown below.

Drag the values to get more ones until we get the positive ones to get a left curve.

We must apply the formula as =mean-standard deviation * 3 to get the exact curves to get the right curve.

After using the formula, the result is shown below.

As in the above data for standard deviation increment to get the left curve, we have incremented the values by 0.1

The same scenario is used by applying the formula as =3.1+STANDARD DEVIATION/10 to get the curve increment of 0.1

After using the formula, the result is shown below.

Drag the values to get the exact result shown in the screenshot below.

Now apply the normal distribution function with the formula = NORM.DIST(DATA value, mean, standard deviation, false).

We will get the below result as follows.

Drag the values to get the exact result which is shown below.

As shown in the above screenshot, we calculated the NORMAL distribution from the mean and standard deviation. Now let’s see what will be the inverse of NORMAL distribution by applying the NORMSINV, which is shown below.

Here, Value Zero (0) has a standard deviation of 7.

Applying scattered graph to look at how the left and right curve appears.

First, select the data and the Normal column.

Go to the Insert tab and select the scattered graph as follows.

We will get the below curve graph as shown below.

Here we can see that Mean value 7 has a standard deviation shape, and we can show that by drawing a straight line to represent it.

Mean =7

1 –Standard deviation indicates 68% of Data.

2 –Standard deviation indicates 95% of Data.

3 –Standard deviation indicates 99.7% of Data.

Normal Distribution Graph:

NORMSINV Graph:

Select the data column and NORM SINV from the above figure to get the graph below.

First, select the data and the Normal column.

Go to the Insert tab and select the scattered graph.

We will get the below graph which is shown in the below screenshot.

From the above screenshot, we can see that we got an exact inverse of a normal distribution which shows the same value figure below.

Example #3 – Configuring the Left and Right Curve

We will configure the left and right curves using the normal distribution function in this example. Consider the data below, where x has negative values and gets incremented to positive values.

Apply the formula =NORM.DIST(A2,0,1,1).

After applying the formula, the result is shown below.

Drag the formula into other cells.

Apply formula =1-B2.

After applying the formula, the result is shown below.

Drag the same formula into other cells.

The result of the above-applied formula is shown below.

Left curve values have been calculated by applying the NORMAL DISTRIBUTION formula by setting the cumulative value as True, and the NORMSINV has been calculated using the left curve.

After applying the formula, the result is shown below.

Drag the same formula into other cells.

As we can see that we got the same value for NORMSINV, which is nothing but the inverse of the normal distribution. In the same way, we will get the right curve value by calculating the 1-left curve value. In the next step, we will check how we get the x’s height using the scattered graph.

Select the left cure and right curve columns.

Go to insert menu.

Select the scattered graph as follows.

We will get the below graph result as shown below.

NORM SINV Graph:

The graph below shows that the NORM DISTRIBUTION value left curve has the exact match for (0, 0.5 ), which lies at the center of the line where we will get the same graph if we apply for NORMDIST.

Here in the above graph it shows very clearly that we got the exact mean at a center point which denotes:

X=0

Left Curve=0.5

Right Curve=0.5

We displayed it to view the NORMSINV values in a graphical format, as shown below.

Things to Remember About Excel NORMSINV

#value! The error occurs when the given argument is a non-numeric or logical value.

In the Normal Distribution function, we usually get #NUM! Error due to the standard deviation argument is less than or equal to zero.

Recommended Articles

This is a guide to Excel NORMSINV. Here we discuss how to use NORMSINV in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

How To Use Excel Conditional Formatting

Imagine examining hundreds of rows of raw data in Excel in an attempt to find a pattern or trend.  You’d go mad!

Thankfully one of the tools we can use to make this task simpler is Conditional Formatting.

The Conditional Formatting features in Excel 2007 are massively improved and now include many default Conditional Formats, including colour scales, icon sets and data bars to name a few. More on these below.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Please enter a valid email address.

Before you read on, download the practice workbook here . Note: This is a .xlsx file. Please ensure your browser doesn’t change the file extension on download.

When to Use Excel’s Conditional Formatting:

If you want to be informed in real time

Answer questions visually

Analyse data: find exceptions, find relationships, find trends, etc

Enhance data presentations

You can choose from inbuilt rules like:

Top 10 or Bottom 10 using percentages, average or item

Greater than, less than or equal to

Text that contains a specific word or phrase

A date occurring

And even identify duplicate values

Or you can insert a formula and create a custom conditional format. More on that later.

Colour coding can be simple like font colour, font style and cell fill, or more elaborate with icons, colour scales or data bars.

Examples How to Apply Conditional Formatting:

Select the range of cells you want formatted.

From the Home tab go to the Styles group and select Conditional Formatting.

The menu will appear with your formatting options.

When you choose one of the options a cascading menu will appear.

Depending on which option you choose you will be prompted to make more selections.

Note: You can specify a custom format or use one of the defaul formats.  You can choose to format the cell fill, font style, colour, size, bold, italic, underline and more.

Remove Rules:

Select Clear Rules. A cascading menu appears.

Choose to clear rules from the entire worksheet or the selected cells.

Manage Rules:

Select Manage Rules from the menu. The Rules Manager dialog box will appear as below.

You can change the list of rules displayed by selecting from the ‘

Show formatting rules for:

’ list.

Guidelines:

1) Conditional formatting is, at its most simple, a format or group of formats. That means if you copy and paste the cell you also copy and paste the conditional format.

2) You can have more than one rule for a cell or range of cells.

3) Rules at the top of the list (as seen in the Rules Manager) take precedence. That is, a rule at the top of the list takes precedence over any rules below it.

5) If rules don’t conflict then both rules will be applied. For example; one rule formats the font colour and the other rule is for the cell fill, both rules can be applied.

6) If rules conflict, for example both rules format the font colour, then the preceding rule, the rule highest in the list, will be applied.

How to Use Stop If True

You can see in the Rules Manager above that to the right of each rule there is a check box for ‘Stop If True’.

We mentioned above that rules take precedence from top to bottom. Therefore if you wanted to stop the formatting once a particular rule was ‘true’ you can simply check the box beside the rule in the Rules Manager.

For example if you checked the box on the first rule and it tested ‘true’ Excel would not continue on with the remaining rules.  This feature enables you to avoid rules that conflict by stopping them at the first occurrence that tests true.

Note: this option isn’t available for colour scales, icon sets or data bars.

Custom Rules:

Whilst the built in formats are great, from time to time you might want to do something different.

You can specify custom conditional formats by selecting New Rule from the Conditional Formatting Menu.  The dialog box below will open.

Then select the type of rule you want, and specify your criteria in the edit the rule description.

Note: Different Rule Description options will appear depending on which Rule Type you select.

Most of them are self explanatory except:

Conditional Formatting Formulas

The last Rule Type in the list above is ‘Use a formula to determine which cells to format’.  Basic examples of formulas you can use here are:

This formula will apply the conditional format to all values greater than 500.

The absolute reference for column F is instructing Excel that the conditional format is dependant on column F.  If you only used a relative reference for the column the formula won’t work properly.

Well, because if you select the whole table before inserting the rule it will highlight the whole row like this:

Here’s a tutorial on how to use formulas to set Conditional Formats.

For Extra Credit add Filters to your data and use the formatting as your filter criteria.

Python String Strip() Function: How To Use?

What is Python strip()?

Python strip() function is a part of built-in functions available in the Python library. The strip() method removes given characters from start and end of the original string. By default, strip() function removes white spaces from start and end of the string and returns the same string without white spaces.

Syntax of the strip() method string.strip([characters])

Parameters

characters: (optional) The given characters will be removed from the start or end of the original string.

If the characters parameter is not given, the whitespaces from the start and end of the string will be removed.

Return Value

The Python String strip() will return:

The original string with white spaces removed from the start and end if the characters to be removed are not specified.

In case the string does not have any whitespaces at the start or end, the string will be returned as it is and will match with the original string.

If the characters parameter is given, and if the characters are given matches, the characters at the start or end of the string will be removed from the original string, and the rest of the string will be returned.

Incase if the characters given does not match the start or the end in the original string, the string will be returned as it is.

Examples of strip() Function in Python Example 1: strip() Method in Python str1 = "Welcome to Guru99!" after_strip = str1.strip()

Output:

Welcome to Guru99!

Example 2: strip() on Invalid Data Type

The Python String strip() function works only on strings and will return an error if used on any other data type like list, tuple, etc.

Example when used on list()

mylist = ["a", "b", "c", "d"] print(mylist.strip())

The above will throw an error :

Traceback (most recent call last): print(mylist.strip()) AttributeError: 'list' object has no attribute 'strip' Example 3: strip() Without character parameter str1 = "Welcome to Guru99!" after_strip = str1.strip() print(after_strip) str2 = "Welcome to Guru99!" after_strip1 = str2.strip() print(after_strip1)

Output:

Welcome to Guru99! Welcome to Guru99! Example 4: strip() Passing character parameters str1 = "****Welcome to Guru99!****" after_strip = str1.strip("*") print(after_strip) str2 = "Welcome to Guru99!" after_strip1 = str2.strip("99!") print(after_strip1) str3 = "Welcome to Guru99!" after_strip3 = str3.strip("to") print(after_strip3)

Output:

Welcome to Guru99! Welcome to Guru Welcome to Guru99!

Why Python strip() function is used?

Here, are reasons for using Python strip function

It helps to remove the characters at the start of the string and also at the end of the string based on the characters given to be removed from the original string.

If the characters given do not match the original string, the string will be returned as it is.

If the characters to be removed are not specified, then the whitespaces from the start and end of the original string will be removed.

If there is no white space at the start or end than the string will be returned as it is.

Summary

The Python String strip() method is part of the built-in function available in python. The function will remove given characters from the start and end of the original string.

This function is very helpful in removing the whitespaces at the start and end of the given string, as shown in the example.

It helps to remove the characters at the start of the string and also at the end of the string based on the characters given to be removed from the original string.

If the characters given do not match the original string, the string will be returned as it is.

If the characters to be removed are not specified, the whitespaces from the start and end of the original string will be removed.

If there is no white space at the start or end than the string will be returned as it is.

Update the detailed information about How To Use Vba Worksheet Function In Excel? on the Eastwest.edu.vn website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!