FIND Function

Hi Friends,

In this article I'll explain how to use FIND function in excel, although we do not use this function in basic excel function but it is very useful in Excel VBA or Excel Macro.

So let us learn it first. As the name of this function suggest it is used to find some text or string within a cell. If FIND function is used nested with IF function then it can help you a lot.

String means a  text, in programming language we use string instead of text, those who are from computer programming background will understand the logic, for now please note String means Text.

Please note that this function is case sensitive. You must type the proper case while searching a text using FIND function.

Description

The Microsoft Excel FIND function returns the location of a substring in a string. The search is case-sensitive.

The FIND function is a built-in function in Excel that is categorized as a String/Text Function The FIND function can be entered as part of a formula in a cell of a worksheet and we will use this property with IF function nested.

Syntax

The syntax for the FIND function in Microsoft Excel is very simple.

=FIND( substring, string, start_position)

Parameters or Arguments

Substring (part of text)

The substring or part of text that you want to find.

String

The string or text to search within.

start_position

Optional. It is the position in string where the search will start. The first position is 1.

Returns

The FIND function returns a numeric value, the first position in the string is 1.

If the FIND function does not find a match, it will return a #VALUE! error.

Examples :

Let's look at some Excel FIND function examples and explore how to use the FIND function as a worksheet function in Microsoft Excel:

FIND Function in Microsoft Excel

Based on the Excel spreadsheet above, the following FIND examples would return:

Case 1 : Finding a particular text/string

            =FIND("Basic",B2,1) in 2nd row or =FIND("blog",B3,1) in 3rd row

FIND Function in Microsoft Excel

Case 2 : Case sensitivity

            I've got an error #VALUE!  -  because I am trying to find 'Fox' in the given text where it is typed as 'fox' and therefore I got an error though the formula is perfect.

            =FIND("Fox",B4,1)

FIND Function in Microsoft Excel

Case 3 : This example is little bit confusing because you can notice that I am finding for 'w' and the result is 5 i.e., 5th position is the first occurrence of 'w', apparently it is wrong. The output is correct.

Here what I am trying to find is the position of 'w' after the first three 'w's. The formula in the cell I typed is =FIND("w",B5,4) meaning that find me the position of 'w' after the fourth character of the provided string. The below image will clarify it.

FIND Function in Microsoft Excel

           

Hope you have enjoyed this article.

Thank you for reading...

Fill Data Automatically Auto Fill Option in Microsoft Excel

Hi Friends,

Today I will discuss about AUTOFILL options in Microsoft Excel. Everybody want a shortcuts in Excel and autofill is such a tool in Excel which surprisingly reduces the manual work that has to be done.

What is AUTOFILL ?

AutoFill is a very useful Excel feature. It allows you to create entire columns or rows of data which are based on the values from other cells. In other words, Excel compares the selected data and tries to guess the next values that will be inserted.

Excel is becoming very intelligent day by day and it will be proved in my next articles.

Actually Excel tries to guess about the work we are doing and tries to help us for example, if you need ten months in words in consecutive ten cells let suppose you type 'January' in first cell and then if you drag the fill handler upto 9 cells the next months like February, 'March' etc will automatically be displayed. Same for numbers also. But there are difficulties. Let us understand it by few examples with images.

 To use the Simple Excel Autofill 

          1.    Excel Cell Fill Handle and Autofill Options Box

          

          2.    Enter a value into the start cell;

          

          3.    Use the mouse to drag the 'fill handle' (the small black square at the bottom right of the start cell) across the range of cells to be filled;

    

         4.    When you drag the 'fill handle' across the range of cells to be filled, Excel will fill the selected cells, by either repeating the value in the first cell or by inserting a sequence from the first cell value (e.g. 1, 2, 3, ...);

        5.    Click on the 'Auto Fill Options' box, which will appear at the end of your selected range of cells. This will give you the following different options:

Simple Excel Autofill

Copy Cells - copy the initial cell across the selected range;

Fill Series - fill the selected range with a series of values (typically increment by 1), starting with the initial cell value;

Fill Formatting Only - fill the selected range with the formatting, but not the values of the initial cell;

Fill Without Formatting - fill the selected range with values, but do not copy the formatting from the initial cell.

Select the option that you required for the filled cells.

Autofill Using More Than One Starting Cell Value

Excel Auto Fill Sequence

If you want Excel to recognize a series that is not a simple increment by 1, this can be done by typing the first two values of your series into the first and second cells of a range. Select both of these cells and again, drag the fill handle across the range to be filled. Excel will automatically recognize the pattern from the two initial cells and continue this across the selected range. Using this method, you can get Excel to fill cells by increments or decrements of any number (e.g. 2, 4, 6, 8, ...).

Autofill Using More Than One Starting Cell Value

Alternatively, if you want Excel to fill cells with repeated alternating values (e.g. 1, 2, 1, 2, 1, 2, ...) you can start off the pattern in the first two (or more) cells, then, with the initial cells highlighted, drag the fill handle and then click on the 'Auto Fill Options' box. Within this box, select the option 'Copy Cells' to repeat the initial cell values across the selected range.

Autofill Dates & Times

Excel Autofill Dates

As dates and times are stored in Excel as numbers, these can also be used with the Excel Autofill.

By default, if you just type in a single date or time and drag the fill handle, dates and times will complete in a series, by adding one day (for dates), or one hour (for times). However, as with simple numbers, you have the option of clicking on the 'Auto Fill Options' box, to select a different type of Auto fill.

Times have the same four Auto fill options as are shown above, for simple numbers (i.e. Copy Cells, Fill Series,Fill Formatting Only, Fill Without Formatting). However, for dates, there are additional Auto Fill options. As well as the four options for simple numbers and times, there are also the following:

Fill Days - Look for a pattern in the day when filling the selected cells;

Fill Weekdays - Look for a pattern in the day when filling the selected cells, but do not include Saturdays or Sundays in the series;

Fill Months - Look for a pattern in the month when filling the selected cells;

Fill Years - Look for a pattern in the year when filling the selected cells.

Autofill Dates & Times

Autofill Functions and Formulas 

The Excel Autofill feature also works with functions and formulas in Excel which is very useful. However, with this type of Autofill, there is no 'series fill' option. Instead, Excel observes the rules of Absolute and Relative Cell References (i.e. if a row or column reference is preceded by a $ sign, excel will keep the reference constant as the formula is copied to other cells; Otherwise, the row or column reference will be adjusted as the formula is copied to other cells.

Horizontal and Vertical Autofill

Excel Autofill Across a Row

As well as working down a column, the Autofill feature also works horizontally, across rows.

Simply drag the fill handle across the cells that you want to populate.

Autofill Multiple Rows or Columns Simultaneously

The Excel Autofill can also handle data in more than one row or column. This is shown in the example below, in which cells A1 and A2 have numeric values 1 and 2, and cells B1 and B2 both have the numeric value 3.

Highlighting cells A1 to B2, and then dragging the fill handle down columns A and B causes the Autofill to complete both columns with their own series (i.e. column A completes with 1, 2, 3, 4, ..., and column B completes with 3, 3, 3, 3, ...)

Autofill Multiple Rows or Columns Simultaneously

Double Click on the Fill Handle

For speed, you can Autofill a column by double-clicking on the fill handle of a highlighted cell (or range of cells). If the cells below or adjacent to the highlighted cell (or range) contain values, double clicking the fill handle causes the Autofill to fill down the current column until it reaches the end of the current data range.

In my next article I will show you how to enter customize list to get these in autofill options.

Thank you for reading ...