Microsoft Excel FIND Function and SEARCH Functions

MS Excel Find Function & Search Functions:

FIND Function is used Locate/Search/Find one text string(Text or Numbers) within another text string, and returns its position from search point.

Find Function is used for Case Sensitive Search.

SEARCH Function is similar to Find Function and is not a Case Specific.

Search Function is a In-Case Sensitive Function.

Syntax:

FIND(Find_Text,Within_Text,Start_Num)  or

SEARCH(Find_Text,Within_Text,Start_Num)

Here :

Find_Text   is the text that you want to Find/Search.

Within_Text   is the text containing the text in which you want to Find/Search.

Start_Num   is  the Search Position Number from which to Start the Search. The position of  first character in Within_Text is 1.

If you omit Start_Num, it is assumed to be 1(i.e., Search starts from beginning.)

Note:

The Search Direction is Left-Right By Default.

Example:

Suppose if Cell A2 has the Main Text String( Eg:TPR-123@CIQ#141-234%143-Excel ) , in which you want to find the position of the your desired string ( Eg: "-" ) you can done in following way as explained in the following example.

Thanks.,
TAMATAM

How To Find Number of Days, Weeks, Months, Quarters Between Two Dates

Excel VBA Macro To Know Number of Days, Weeks, Months, Quarters Between Two Dates.

You can also know the Names of Days, Weeks, Months  Between Two Dates

Sub DatesDiff()

Dim X As Date 'Or You Can Declare as String

Dim Y As Date 'Or You Can Declare as String

Dim DD As String

X = "01-May-2012" 'Or You Can Declare as #01-May-2012#

Y = "01-AUG-2012" 'Or You Can Declare as #01-AUG-2012#

'To Know No.of Days Between Two Dates

DD = DateDiff("D", X, Y)

MsgBox "The No.of Days Between" & "  " & X & " and " & Y & "  " & " Are :" & DD

DD = DateDiff("Y", X, Y)

MsgBox "The No.of Days Between" & "  " & X & " and " & Y & "  " & " Are :" & DD

'To Know No.of Months Between Two Dates

DD = DateDiff("M", X, Y)

MsgBox "The No.of Months Between" & "  " & X & " and " & Y & "  " & " Are :" & DD

'To Know No.of Weeks Between Two Dates

X = "01-May-2010"

Y = "01-MAY-2012"

DD = DateDiff("WW", X, Y)

MsgBox "The No.of Weeks Between" & "  " & X & " and " & Y & "  " & " Are :" & DD

'To Know No.of Weekdays(Suppose 'Saturday'as on 01-May2010) Between Two Dates

X = "01-May-2010"

Y = "01-MAY-2012"

DD = DateDiff("W", X, Y)

MsgBox "The No.of ThisWeekDays Between" & "  " & X & " and " & Y & "  " & " Are :" & DD

'To Know No.of Years Between Two Dates

X = "01-May-2010"

Y = "01-MAY-2012"

DD = DateDiff("YYYY", X, Y)

MsgBox "The No.of Years Between" & "  " & X & " and " & Y & "  " & " Are :" & DD

'To Know No.of Quarters Between Two Dates

X = "01-May-2010"

Y = "01-MAY-2012"

DD = DateDiff("Q", X, Y)

MsgBox "The No.of Quarters Between" & "  " & X & " and " & Y & "  " & " Are :" & DD

End Sub

Thanks.,

TAMATAM

Excel VBA Macros Developer

How to Find Number of Days, Weeks, Months, Quarter In the Given Date Year using Excel VBA Macro

Excel VBA Macro To Know Number of Days, Weeks, Months, Quarter In the Given Date Year.

You can also know the Names of the Days, Weeks, Months In the Given Date

Sub MyYear_Month_DayNames()

Dim X As String

Dim Y As String

X = #10/24/2012#

'To Know Week Number (Eg: 4th Week) In the Current Year as of Given Date

Y = Weekday(X)

MsgBox "Week No.of The Month In Given Date " & " " & X & " " & " Is: " & Y

'To Know  Weekday Name of the Given Date

Y = WeekdayName(Weekday(X))

MsgBox "Week Name of The Month In Given Date " & " " & X & " " & " Is: " & Y

'To Know Month Number of the Given Date

Y = Month(X)

MsgBox "Month Number of The Month In Given Date " & " " & X & " " & " Is: " & Y

'To Know Month Name of the Given Date

Y = MonthName(Month(X))

MsgBox "Month Name of The Month In Given Date " & " " & X & " " & " Is: " & Y

'To Know Year of the Given Date

Y = Year(X)

MsgBox "Year of The Given Date " & " " & X & " " & " Is: " & Y

'To Know No. of Days of the Given Date

Y = Day(X)

MsgBox "The No. of Days in Given Date" & "  " & X & " Are: " & Y

'To Know Current Quarter of the Year as of Given Date

Y = DatePart ("Q", X)

MsgBox "The Current Quarter of the Year In Given Date" & "  " & X & " Is: " & Y

End Sub

How To Rename and Move A File to Another Location using Excel VBA Macro

Excel VBA Macro To Rename and Move A File to Another Location
Sub Rename_Move_One_File()
Dim MyFilePath As String
Dim MyTargetFolder As String

MyFilePath = "C:\Documents and Settings\Administrator\My Documents\SourceFolder\"
MyTargetFolder = "F:\TargetFolder\"

'You can change the Path and File Name

Name MyFilePath & "Sample.xlsx" As MyTargetFolder & " NewSample.xlsx"
End Sub

Thanks.,

TAMATAM.,

 (:-------<Dear Users/Visitors>--------:) - Keep On Following My Blog

How To Copy a File From One Path to Another Path

Excel VBA Macro To Copy  a File From One Path to Another Path

Sub Copy_All_Folder_Files()
Dim SourceFolderPath As String
Dim TargetFolderPath As String
Dim FSO As Object

SourceFolderPath = "C:\Documents and Settings\Administrator\My Documents\SourceFolder"

'File Path Should Specify Exactly

'TargetFolderPath = "C:\Documents and Settings\Administrator\My Documents"
TargetFolderPath = "F:\TargetFolder"

If Right(SourceFolderPath, 1) = "\" Then
SourceFolderPath = Left(SourceFolderPath, Len(SourceFolderPath) - 1)
End If

'If Right(TargetFolderPath, 1) = "\" Then
'TargetFolderPath = Left(TargetFolderPath, Len(TargetFolderPath) - 1)
'End If

Set FSO = CreateObject("Scripting.FileSystemObject")

If FSO.FolderExists(SourceFolderPath) = False Then
MsgBox ("Source Folder Does Not Exist or Path Not Found")
End If

If FSO.FolderExists(TargetFolderPath) = False Then
MsgBox ("Target Folder Does Not Exist or Path Not Found")
End If

On Error Resume Next

FSO.CopyFolder SourceFolderPath, TargetFolderPath
MsgBox "SuccessFull Copied"

End Sub

How to Copy or Move a File From One Path to another Path using Excel VBA Macro

Excel VBA Macro To Copy or Move a File From One Directory or Folder to Another

Sub Copy_One_File()

Dim SourceFilePath As String
Dim TargetFolderPath As String
Dim FSO As Object

Set FSO = CreateObject("Scripting.FileSystemObject")

SourceFilePath = "C:\Documents and Settings\Administrator\My Documents\SourceFolder\Sample.xlsx"

'File Path Should Specify Exactly With File Extension

TargetFolderPath = "C:\Documents and Settings\Administrator\My Documents\TargetFolder\"

If Right(TargetFolderPath, 1) <> "\" Then
TargetFolderPath = TargetFolderPath & "\"
End If

If FSO.FileExists(SourceFilePath) = False Then
MsgBox ("Source File Does Not Exist or Path Not Found")
End If

On Error Resume Next

FSO.CopyFile SourceFilePath, TargetFolderPath
MsgBox "SuccessFull Copied"

'FSO.MoveFile SourceFilePath, TargetPath  'To Move
'MsgBox "SuccessFull Moved"

End Sub

How To Open a Special Folder

Excel VBA Macro To Open a Special Folder
Sub GetSpecialFolder()
'Special Folders are : 
'AllUsersDesktop, AllUsersStartMenu
'AllUsersPrograms, AllUsersStartup, Desktop, Favorites
'Fonts, MyDocuments, NetHood, PrintHood, Programs, Recent
'SendTo, StartMenu, Startup, Templates

'Get Favorites Folder and Open it
Dim WShell As Object
Dim SpecialPath As String
  
Set WShell = CreateObject("WScript.Shell")
SpecialPath = WShell.SpecialFolders("Favorites")
MsgBox SpecialPath
'Open Folder in Explorer
Shell "Explorer.Exe " & SpecialPath, vbNormalFocus
       
'The WindowStyle Named Argument has These Values:
'Constant Value Description
'vbHide : 0 Window is hidden and focus is passed to the hidden window. The vbHide constant is not applicable on Macintosh platforms.
'vbNormalFocus : 1 Window has focus and is restored to its original size and position.
'vbMinimizedFocus : 2 Window is displayed as an icon with focus.
'vbMaximizedFocus : 3 Window is maximized with focus.
'vbNormalNoFocus : 4 Window is restored to its most recent size and position. The currently active window remains active.
'vbMinimizedNoFocus : 6 Window is displayed as an icon. The currently active window remains active.

End Sub

How to Delete Whole Folder With All Files

Excel VBA Macro to Delete Whole Folder With All Files
Sub Delete_Whole_Folder()

    Dim FSO As Object
    Dim MyPath As String

    Set FSO = CreateObject("Scripting.FileSystemObject")

    MyPath = "C:\Documents and Settings\Administrator\My Documents\Sample"
'Sample is the Folder to Delete
    If Right(MyPath, 1) = "\" Then
        MyPath = Left(MyPath, Len(MyPath) - 1)
    End If

    If FSO.FolderExists(MyPath) = False Then
        MsgBox MyPath & " Folder Doesn't Exist In Given Patah", , "Filder Does Not Exit"
        Exit Sub
    End If

    FSO.DeleteFolder MyPath

End Sub

How To Delete all Files and SubFolders In a Folder

Macro To Delete all Files and SubFolders In a Folder
'Be Sure That No File is Open in The Folder
Sub Clear_All_Files_SubFolders_In_Folder()

    Dim FSO As Object
    Dim MyPath As String

    Set FSO = CreateObject("Scripting.FileSystemObject")

MyPath = "C:\Documents and Settings\Administrator\My Documents\Sample"

    If Right(MyPath, 1) = "\" Then
        MyPath = Left(MyPath, Len(MyPath) - 1)
    End If

    If FSO.FolderExists(MyPath) = False Then
        MsgBox MyPath & " Folder Doesn't Exist"
        Exit Sub
    End If

    On Error Resume Next
    'Delete Files
    FSO.DeleteFile MyPath & "\*.*", True
    'Delete SubFolders
    FSO.DeleteFolder MyPath & "\*.*", True

End Sub

Excel Index Match VLookUp Function with Multiple Criteria to LookUp Right to Left

Look Up Based on Multiple Criteria using Index - Match Functions

We can use Index-Match Functions combination to lookup a desired value in a range (list of data) based on multiple criteria.

By Using INDEX and Match Functions Combination we can lookup up a value in any direction of the Table of Data.

In this Formula you can specify any no. of criteria based on your requirement.

This Formula vs. VLOOKUP

The Vlookup Function only searches for a value in the first column of a table array and returns a value in the same row from another column in the table array. .

.i.e., VLookup Function works in Left-Right direction only.

Example-I:

Let us suppose there are three Parent Companies (say 123, 143, 456) which have three different subsidiaries in three different countries (say India, China, America).

Suppose now if you want lookup a Subsidiary Name of a Particular Parent in the Particular Country we can find as in the following manner.

FORMULA:

{=INDEX (C2:C10, MATCH (1,(E2:E10=C13)*(D2:D10=D13),0),0)}

Here:

>> C2:C10 is the range where you have the subsidiary names, from which you want extract/lookup, one that is in the index of a selected Parent Co. and Country.

>> E2:E10 is the range where you have the Parent Companies Names.

>> D2:D10 is the range where you have the Country Names.

>> MATCH (1, (E2:E10=C13)*(D2:D10=D13), 0), is the Match Function which refers to row number of the Index Function. Here  if the value in E2:E10 is equals/matches to ‘C13’ and(* refers to AND) D2:D10 equals/matches to ‘D13’ then value in the corresponding row of C2:C10 is the Lookup value.

In this Formula you can specify any no. of criteria based on your requirement.

Note:

>>The above formula is an Array formula so that we should press ‘Ctrl+ Shift+ Enter’ to get the desired result otherwise we will get an  error ‘#VALUE’.

Thanks,

TAMATAM

Excel VLOOKUP Function Examples in Different Models

Excel VLOOKUP Function: 

Vlookup Funciton searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

Syntax :

=VLOOKUP(lookupValue,TableArray,ColIndexNum,RangeLookup)

Where: 

LookupValue : The value to search in the first column of the table array. LookupValue can be a value or a reference. If LookupValue is smaller than the smallest value in the first column of TableArray, VLOOKUP returns the #N/A error value.

TableArray : Two or more columns of data. Used as a reference to a range or a range name. The values in the first column of TableArray   are the values searched by lookupValue. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.

ColIndexNum : The column number in TableArray   from which the matching value must be returned. A ColIndexNum of 1 returns the value in the first column in TableArray  ; a ColIndexNum of 2 returns the value in the second column in TableArray  , and so on. If ColIndexNum is:

Less than 1, VLOOKUP returns the #VALUE! error value.

If ColIndexNum  is Greater than the number of columns in TableArray  , VLOOKUP returns the #REF! error value.

RangeLookup : A logical value that specifies whether you want  VLOOKUP to find an exact match or an approximate match:

If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than LookupValue is returned.

The values in the first column of TableArray   must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. For more information.

If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of TableArray   do not need to be sorted. If there are two or more values in the first column of TableArray   that match the lookupValue, the first value found is used. If an exact match is not found, the error value #N/A is returned.

Remarks

Remarks :

Ø  When searching text values in the first column of table_array, ensure that the data in the first column of table_array does not have leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP may give an incorrect or unexpected value. For more information, see CLEAN and TRIM Funcitons.

Ø  When searching number or date values, ensure that the data in the first column of table_array is not stored as text values. In this case, VLOOKUP may give an incorrect or unexpected value. For more information, see Convert numbers stored as text to numbers.

Ø  If range_lookup is FALSE and lookup_value is text, then you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

---------------------------------------------------------------------------------------------------------------- 

Examples :

---------------------------------------------------------------------------------------------------------------- 

Vlookup-Basic Model  1 :

Let  us suppose there seven months as Jan ……June , Ten names as Name1…..Name10 .

Now if you want to lookup the value of a particular name (say Name5) in the desired column(say Apr).Then you can find  using simple Vlookup Function  in the following manner( explained in the following screen shots) .

---------------------------------------------------------------------------------------------------------------- 

Model 2 - Vlookup with Match Function:

Let  us suppose there seven months as Jan ……June , Ten names as Name1…..Name10 .

Now if you want to lookup the value of a particualar name (say Name5) and particualar month(say Apr) selected from the list of names  and months availble.Then you find  using Vlookup and Match Functions Combination in the following manner( explained in the following screen shots) .

Syntax:

=VLOOKP(B14,A2:G11,MATCH(C13,A1:G1,0),0)

Explanation:

Here the above formula will loolup  the cell ‘B14’ value in the table array ‘A2:G11’ and returns a value that matches to cell ‘C13’.

---------------------------------------------------------------------------------------------------------------- 

Model 3 -Vlookup with Columns Function:

Syntax:

=VLOOKUP(B14,A2:G11,COLUMNS(A:G),0)

Explanation:

Here the COLUMNS (A:G) function returns values form the column ‘G’  which matches to the lookup value B14(Name 7).

In the same way if you take COUMNS(A:G) it will return the value from the column ‘G’ which Macthes to the lookup value.

Thanks,

TAMATAM

Date and Time Functions In VBA

The Date and Time Functions:

Function	Description
Now	This Function Returns Current date and time. Example: 1/7/12 5:16:38 pm returned by Now
Date	This Function Returns Current date only. Example: 1/7/12 returned by Date
Time	This Function Returns Current time only. Example: 5:16:38 pm returned by Time
Timer	This Function Returns Number Of Seconds Since Midnight. Example: 5:16:38 Pm Returned By Timer
Timevalue()	This Function Returns Time part of argument. Example: 5:16:38 pm returned by Timevalue(Now)
Datevalue()	This Function Returns Date part of argument (Used for ordering by date) Example: select * from TblPeople order by datevalue(review)
Dateserial()	This Function Returns Date Part Of Three Arguments: Year, Month, Day Example: Having Invoicedate <= Dateserial(Year(Now), Month(Now)-1, Day(Now)) Dateserial Handles January Correctly In The Above Example
Datepart()	This Function Returns Returns A Portion Of The Date. Year example: 2012 Returned By Datepart('YYYY', Date) Month Example: 10 returned By Datepart('M', #10/11/2012#) Week Of Year Example: 41 returned by Datepart('ww', #10/11/2012#) Day Of Week Example: Monday Returned By Datepart('D', #6/3/2012#) Quarter example: 4 returned by Datepart('Q', #10/11/2012#)
Year()	This Function Returns the Year Portion of the date argument.
Month()	This Function Returns the Month Portion of the date argument.
Day()	This Function Returns the Day Portion of the date argument.
Monthname()	This Function Used to Format Month Names. July returned by Monthname(month(date))
Weekdayname()	This Function Used To Format Day Names. Wednesday returned by WeekdayName(Weekday(Date))
Today()	This Function  Returns Current date only; used in Excel, not available in Access.
Datediff()	This Function  Returns The Difference In Dates. Days example: -656 returned by Datediff("D", #10/11/2001#, #12/25/1999#) Months Example: 1 returned by Datediff("M", #8/10/2000#, #9/14/2012#) Days Example: 0 returned by Datediff("M", date1, date2) 0 is returned above only if the two dates have same month and year
Dateadd()	This Function Add and Subtract Dates. 10/11/2002 returned by Dateadd("yyyy", 1, #10/11/2012#)) Today's Date + 30 Days Returned By Dateadd("d", 30, date) The date 45 days ago returned by Dateadd("D", -45, Date) To Find Monday Of A Week: Dateadd("D",-Weekday(Date)+2,Date)
Format()	This Function Very useful for Formatting Dates. Examples: Wed, 5-July-2012 Returned By Format(Date,"DD-MMMM-YYYY") 5-Jul-12  Returned By Format(Date,"D-MM-YY")

Thanks.,
TPR