Excel VBA LEN Function to measure length of text

This Excel VBA tutorial explains how to use LEN Function to measure length of text.

Excel VBA LEN Function

Excel VBA LEN Function is to measure the length of text, to be more precise, the number of characters in a text.

LEN Function is most useful as a tool for data validation where length of text always have limit in a system.

LEN Function is also commonly used in conjunction with other functions:

LEFT Function – extract a substring on the left

RIGHT Function – extract a substring on the right

MID Function – extract a substring in the middle of the string

INSTR Function – return starting position of a substring (first occurance) in a string

Syntax of VBA Excel LEN Function

The Syntax of VBA LEN Function is same as that in spreadsheet.

LEN(text)
textThe text string from which you want to return length

Example of Excel VBA LEN Function

Example 1

Suppose Cell A1 contains address Flat A, ABC Street,

Cell B1 contains a full name Peter, Gilbert

FormulaResultExplanation
LEN(Range(“A1”).Value)19Total 19 characters in the string (including space)
LEFT(Range(“A1”).Value,LEN(Range(“A1”).Value)-1)Flat A, ABC StreetExtract substring from left excluding the ending comma
MID(Range(“B1”).Value,INSTR(Range(“B1″).Value,”,”)+1,LEN(Range(“B1”).Value))GilbertExtract substring from (comma position+1) to (length of whole string)

Example 2

The below example demonstrates how to count number of “e” in the string “Peter, Gilbert” by looping through each character in a string.

Public Sub countE()
    Dim Val As String
    Val = "Peter, Gilbert"
    For i = 1 To Len(Val)
        If Mid(Val, i, 1) = "e" Then
            counter = counter + 1
        End If
    Next i
    MsgBox (counter)
End Sub

 

Access Excel VBA MID Function to extract substring

This tutorial explains how to use MID Function to extract substring from a string in Excel / Access VBA

Excel / Access VBA MID Function

MID Function is quite similar to LEFT Function and RIGHT Function, where RIGHT Function extracts a substring on the right, LEFT Function extracts a substring on the left, while MID extracts a substring in the middle of the string.

MID Function is commonly used with INSTR Function, which returns the position of the string (In Excel worksheet, use Search Function or Find Function instead of INSTR Function). For example, I want to extract the last name of a person (Gilbert) from the full name (Peter, Gilbert), which is separated by a comma. First thing I need to do is to locate the position of the comma using INSTR Function, and then extract the last name with MID Function (see the below example).

Syntax of VBA Excel MID Function

MID( text, start_position, number_of_characters )
textThe text string from which you want to extract the characters
start_positionThe position of the first character you want to extract. Positions start at 1.

number_of_character

The number of characters to return.

Example of  VBA Excel MID Function

FormulaResultExplanation
MID(“Access Excel.Tips”,1,6)AccessExtract substring from position 1 to 6
Mid(“Peter,Gilbert”, 1, InStr(1, “Peter,Gilbert”, “,”) – 1)PeterExtract substring from position 1 to (comma position-1)
Mid(“Peter,Gilbert”, InStr(1, “Peter,Gilbert”, “,”) + 1, Len(“Peter,Gilbert”))GilbertExtract substring from (comma position+1) to (length of whole string)

Outbound References

http://www.techonthenet.com/excel/formulas/mid.php

 

Excel Find Function

This Excel tutorial explains how to use Excel FIND Function for Excel worksheet, and also explains the differences between INSTR and SEARCH function.

You may also want to read:

Extract text in brackets

Remove text in brackets

Excel Find Function

Excel Find function is used to search a substring (part of the string) within a string and return the position of the first occurrence, note that Excel Find function is case sensitive. Excel Search function is very similar to Find function, but they are different in the way that Search function can use Wildcard and is case insensitive.

For example, in the string “FinanceDepartment”, the substring “Department” can be found at 8th position within “FinanceDepartment”, the function will return number “8”.

If we try to search substring “Division” within “FinanceDepartment”, since it cannot be found, error #VALUE! will return, but you can get rid of the error using IFERROR Function, I will show an example below.

For Excel VBA, please use Instr Function.

Syntax of Excel Find Function

Find( substring, string, [start_position] )
substringpart of the string you want to search
stringthe full string to be searched
[start_position]Optional. It is the starting position for the search, default i s 1. For example, if you want to search “De” from “Development Department”, beginning from the 13th position, the function will return 13, not 1.

Excel VBA does not have FIND or SEARCH function (only FIND Method for Range), but VBA Function INSTR is very similar to FIND, but they are different in the way that INSTR allows case sensitive or case insensitive comparison and return 0 if substring is not found. INSTR function also allows Wildcard but not Find function.

Example of Excel Find Function

FunctionResultExplanation
FIND(“2″,”123123”,3)5The first occurrence of “2” in “123123” starting from position 3 is at position 5
FIND(“f”,“FINANCE”)#VALUE!Since FIND Function is case sensitive, “f” cannot be found
FIND(“D”,“FINANCE”)#VALUE!“D” not found in substring
IFERROR(FIND(“D”,”FINANCE”),”error”)errorCapture error to prevent error #VALUE!

 Outbound References

http://www.techonthenet.com/excel/formulas/find.php
https://www.youtube.com/watch?v=2R2OYJbKHlA

Excel Search Function

This Excel tutorial explains the Excel Search Function for Excel worksheet, and also explains the differences between INSTR and FIND function.

Excel Search Function

Excel Search function allows Wildcard to search a substring (part of the string) within a string and return the position of the first occurrence. Excel Search Function is not case sensitive, you should consider using Excel FIND Function for case sensitive comparison, but FIND Function cannot use Wildcard.

For example, in the string “FinanceDepartment”, the substring “Department” can be found at 8th position within “FinanceDepartment”, the function will return number “8”.

If we try to search substring “Division” within “FinanceDepartment”, since it cannot be found, error #VALUE! will return, but you can get rid of the error using IFERROR Function, I will show an example below.

For Excel VBA, please use Instr Function.

Syntax of Excel Search Function

SEARCH( substring, string, [start_position] )

substring is part of the string you want to search, you can use Wildcard characters.

WildcardMeaningExample
*Represents one or more characters (any character)J*     any text that starts with J
*J     starts with any text but ends with J
*J*   any text that has J in the middle
?Represents one character (any character)J?     2 characters that start with J
?J     2 characters that end with J
?J?   3 characters with J in the middle
~Treat * or ? as the actual character but not wildcard. Used ~ followed by * or ?J~**   any text that starts with J*
~**J   any text that starts with * and ends with J
~?*~* any text that starts with ? and ends with *

string is the full string

[start_position] is optional. It is the starting position for the search, default i s 1. For example, if you want to search “De” from “Development Department”, beginning from the 13th position, the function will return 13, not 1.

Excel VBA does not have SEARCH or FIND function (only FIND Method for Range), but VBA Function INSTR is very similar to SEARCH, but they are different in the way that INSTR allows case sensitive or case insensitive comparison and return 0 if substring is not found.

Example of Excel Search Function

FunctionResultExplanation
SEARCH(“2″,”123123”,3)5The first occurrence of “2” in “123123” starting from position 3 is at position 5
SEARCH(“f”,“FINANCE”)1Since Search Function is not case sensitive, “f” can be found in position 1
SEARCH(“D”,“FINANCE”)#VALUE!“D” not found in substring
SEARCH(“AN*”,“FINANCE”)4Search a substring position that starts with AN
IFERROR(SEARCH(“D”,”FINANCE”),”error”)errorCapture error to prevent error #VALUE!

In order to prevent error when string is not found, we can either use ISERROR or ISNUMBER to check the error.

=IF(ISERROR(SEARCH("D","FINANCE")),"string cannot be found","string can be found")
=IF(ISNUMBER(SEARCH("D","FINANCE")),"string can be found","string cannot be found")

Logically I would prefer ISNUMBER.

Outbound References

http://www.techonthenet.com/excel/formulas/search.php

https://www.youtube.com/watch?v=fk3MDE71cgw