$show=home

VBA - Strings

VBA - Strings Shout4Education
Strings are a sequence of characters, which can consist of either alphabets, numbers, special characters, or all of them. A variable is said to be a string if it is enclosed within double quotes " ".

Syntax

variablename = "string"

Examples

str1 = "string"   ' Only Alphabets
str2 = "132.45"   ' Only Numbers
str3 = "!@#$;*"  ' Only Special Characters
Str4 = "Asc23@#"  ' Has all the above

String Functions

There are predefined VBA String functions, which help the developers to work with the strings very effectively. Following are String methods that are supported in VBA. Please click on each one of the methods to know in detail.
Sr.No.Function Name & Description
1InStr
Returns the first occurrence of the specified substring. Search happens from the left to the right.
2InstrRev
Returns the first occurrence of the specified substring. Search happens from the right to the left.
3Lcase
Returns the lower case of the specified string.
4Ucase
Returns the upper case of the specified string.
5Left
Returns a specific number of characters from the left side of the string.
6Right
Returns a specific number of characters from the right side of the string.
7Mid
Returns a specific number of characters from a string based on the specified parameters.
8Ltrim
Returns a string after removing the spaces on the left side of the specified string.
9Rtrim
Returns a string after removing the spaces on the right side of the specified string.
10Trim
Returns a string value after removing both the leading and the trailing blank spaces.
11Len
Returns the length of the given string.
12Replace
Returns a string after replacing a string with another string.
13Space
Fills a string with the specified number of spaces.
14StrComp
Returns an integer value after comparing the two specified strings.
15String
Returns a string with a specified character for specified number of times.
16StrReverse
Returns a string after reversing the sequence of the characters of the given string.

VBA - Instr

The InStr Function returns the first occurrence of one string within another string. The search happens from the left to the right.

Syntax

InStr([start,]string1,string2[,compare])

Parameter Description

  • Start − An optional parameter. Specifies the starting position for the search. The search begins at the first position from the left to the right.
  • String1 − A required parameter. String to be searched.
  • String2 − A required parameter. String against which String1 is searched.
  • Compare − An optional parameter. Specifies the string comparison to be used. It can take the following mentioned values.
    • 0 = vbBinaryCompare - Performs Binary Comparison (Default)
    • 1 = vbTextCompare - Performs Text Comparison

Example

Add a button and add the following function.
Private Sub Constant_demo_Click() 
   Dim Var As Variant 
   Var = "Microsoft VBScript" 
   MsgBox ("Line 1 : " & InStr(1, Var, "s")) 
   MsgBox ("Line 2 : " & InStr(7, Var, "s")) 
   MsgBox ("Line 3 : " & InStr(1, Var, "f", 1)) 
   MsgBox ("Line 4 : " & InStr(1, Var, "t", 0)) 
   MsgBox ("Line 5 : " & InStr(1, Var, "i")) 
   MsgBox ("Line 6 : " & InStr(7, Var, "i")) 
   MsgBox ("Line 7 : " & InStr(Var, "VB")) 
End Sub 
When you execute the above function, it produces the following output.
Line 1 : 6
Line 2 : 0
Line 3 : 8
Line 4 : 9
Line 5 : 2
Line 6 : 16
Line 7 : 11

VBA -InString Reverse

The InStrRev function returns the first occurrence of one string within another string. The Search happens from the right to the left.

Syntax

InStrRev(string1,string2[,start,[compare]])

Parameter Description

  • String1 − A required parameter. String to be searched.
  • String2 − A required parameter. String against which String1 is searched.
  • Start − An optional parameter. Specifies the starting position for the search. The search begins at the first position from the right to the left.
  • Compare − An optional parameter. Specifies the string comparison to be used. It can take the following mentioned values.
    • 0 = vbBinaryCompare - Performs Binary Comparison (Default)
    • 1 = vbTextCompare - Performs Text Comparison

Example

Add a button and place the following function.
Private Sub Constant_demo_Click()
   var = "Microsoft VBScript"
   msgbox("Line 1 : " & InStrRev(var,"s",10))
   msgbox("Line 2 : " & InStrRev(var,"s",7))
   msgbox("Line 3 : " & InStrRev(var,"f",-1,1))
   msgbox("Line 4 : " & InStrRev(var,"t",5))
   msgbox("Line 5 : " & InStrRev(var,"i",7))
   msgbox("Line 6 : " & InStrRev(var,"i",7))
   msgbox("Line 7 : " & InStrRev(var,"VB",1))
End Sub
Upon executing the above script, it produces the following result.
Line 1 : 6
Line 2 : 6
Line 3 : 8
Line 4 : 0
Line 5 : 2
Line 6 : 2
Line 7 : 0 

VBA - Lcase

The LCase function returns the string after converting the entered string into lower case letters.

Syntax

Lcase(String)

Example

Add a button and place the following function inside the same.
Private Sub Constant_demo_Click()
   var = "Microsoft VBScript"
   msgbox("Line 1 : " & LCase(var))
   
   var = "MS VBSCRIPT"
   msgbox("Line 2 : " & LCase(var))
   
   var = "microsoft"
   msgbox("Line 3 : " & LCase(var))
End Sub
Upon executing the above script, it produces the following output.
Line 1 : microsoft vbscript
Line 2 : ms vbscript
Line 3 : microsoft

VBA - UCase

The UCase function returns the string after converting the entered string into UPPER case letters.

Syntax

UCase(String)

Example

Add a button and place the following function inside the same.
Private Sub Constant_demo_Click()
   var = "Microsoft VBScript"
   msgbox("Line 1 : " & UCase(var))
   
   var = "MS VBSCRIPT"
   msgbox("Line 2 : " & UCase(var))
   
   var = "microsoft"
   msgbox("Line 3 : " & UCase(var))
End Sub
Upon executing the above script, it produces the following output.
Line 1 : MICROSOFT VBSCRIPT
Line 2 : MS VBSCRIPT
Line 3 : MICROSOFT

VBA - Left

The Left function returns a specified number of characters from the left side of the given input string.

Syntax

Left(String, Length)

Parameter Description

  • String − A required parameter. Input String from which the specified number of characters to be returned from the left side.
  • Length − A required parameter. An Integer, which specifies the number of characters to be returned.

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim var as Variant
   
   var = "Microsoft VBScript"
   msgbox("Line 1 : " & Left(var,2))
   
   var = "MS VBSCRIPT"
   msgbox("Line 2 : " & Left(var,5))
   
   var = "microsoft"
   msgbox("Line 3 : " & Left(var,9))
End Sub
When you execute the above function, it produces the following output.
Line 1 : Mi
Line 2 : MS VB
Line 3 : microsoft


VBA - Right

The Right function returns a specified number of characters from the right side of the given input string.

Syntax

Right(String, Length)

Parameter Description

  • String − A required parameter. Input String from which the specified number of characters to be returned from the right side.
  • Length − A required parameter. An Integer, which Specifies the number of characters to be returned.

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   var = "Microsoft VBScript"
   msgbox("Line 1 : " & Right(var,2))
   
   var = "MS VBSCRIPT"
   msgbox("Line 2 : " & Right(var,5))
   
   var = "microsoft"
   msgbox("Line 3 : " & Right(var,9))
End Sub
When you execute the above function, it produces the following output.
Line 1 : pt
Line 2 : CRIPT
Line 3 : microsoft


VBA - Mid

The Mid Function returns a specified number of characters from a given input string.

Syntax

Mid(String,start[,Length])

Parameter Description

  • String − A required parameter. Input String from which the specified number of characters to be returned.
  • Start − A required parameter. An Integer, which specifies the starting position of the string.
  • Length − An optional parameter. An Integer, which specifies the number of characters to be returned.
Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim var as Variant
   var = "Microsoft VBScript"
   msgbox("Line 1 : " & Mid(var,2))
   msgbox("Line 2 : " & Mid(var,2,5))
   msgbox("Line 3 : " & Mid(var,5,7))
End Sub
When you execute the above function, it produces the following output.
Line 1 : icrosoft VBScript
Line 2 : icros
Line 3 : osoft V

VBA - Ltrim

The Ltrim function removes the blank spaces from the left side of the string.

Syntax

LTrim(String)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim var as Variant
   var =       "             Microsoft VBScript"
   msgbox "After Ltrim : " & LTrim(var)
End Sub
When you execute the function, it produces the following output.
After Ltrim : Microsoft VBScript


VBA - Rtrim

The Rtrim function removes the blank spaces from the right side of the string.

Syntax

RTrim(String)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim var as Variant
   var =       "Microsoft VBScript           "
   msgbox("After Rtrim : " & RTrim(var))
End Sub
When you execute the above function, it produces the following output.
After Rtrim : Microsoft VBScript


VBA - Trim

The Trim function removes both the leading and the trailing blank spaces of the given input string.

Syntax

Trim(String)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   var = "Microsoft VBScript"
   var =       "            Microsoft VBScript           "
   msgbox ("After Trim : " & Trim(var))
End Sub
When you execute the above function, it produces the following output.
After trim : Microsoft VBScript

VBA - Len

The Len function returns the length of the given input string including the blank spaces.

Syntax

Len(String)

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim var1 as Variant
   Dim var2 as Variant
   
   var1 ="Microsoft VBScript"
   msgbox("Length of var1 is : " & Len(var1))
  
   var2 =       "       Microsoft VBScript           "
   msgbox ("Length of var2 is : " & Len(var2))
End Sub
When you execute the above function, it produces the following output.
Length of var1 is : 18
Length of var2 is : 36

VBA - Replace

The Replace function replaces a specified part of a string with a specific string, a specified number of times.

Syntax

Replace(string,find,replacewith[,start[,count[,compare]]]) 

Parameter Description

  • String − A required parameter. The Input String which is to be searched for replacing.
  • Find − A required parameter. The part of the string that will be replaced.
  • Replacewith − A required parameter. The replacement string, which would be replaced against the find parameter.
  • Start − An optional parameter. Specifies the start position from where the string has to be searched and replaced. Default value is 1.
  • Count − An optional parameter. Specifies the number of times the replacement has to be performed.
  • Compare − An optional parameter. Specifies the comparison method to be used. Default value is 0.
    • 0 = vbBinaryCompare - Performs a binary comparison
    • 1 = vbTextCompare - Performs a Textual comparison

Example

Private Sub Constant_demo_Click()
   Dim var as Variant
   var = "This is VBScript Programming"
  
   'VBScript to be replaced by MS VBScript
   msgbox("Line 1: " & Replace(var,"VBScript","MS VBScript"))
  
   'VB to be replaced by vb
   msgbox("Line 2: " & Replace(var,"VB","vb"))
  
   ''is' replaced by ##
   msgbox("Line 3: " & Replace(var,"is","##"))
   
   ''is' replaced by ## ignores the characters before the first occurence
   msgbox("Line 4: " & Replace(var,"is","##",5))
   
   ''s' is replaced by ## for the next 2 occurences.
   msgbox("Line 5: " & Replace(var,"s","##",1,2))
  
   ''r' is replaced by ## for all occurences textual comparison.
   msgbox("Line 6: " & Replace(var,"r","##",1,-1,1))
  
   ''t' is replaced by ## for all occurences Binary comparison
   msgbox("Line 7: " & Replace(var,"t","##",1,-1,0))
  
End Sub
When you execute the above function, it produces the following output.
Line 1: This is MS VBScript Programming
Line 2: This is vbScript Programming
Line 3: Th## ## VBScript Programming
Line 4: ## VBScript Programming
Line 5: Thi## i## VBScript Programming
Line 6: This is VBSc##ipt P##og##amming
Line 7: This is VBScrip## Programming

VBA - Space

The Space function fills a string with a specific number of spaces.

Syntax

space(number)

Parameter Description

Number − A required parameter. The number of spaces that we want to add to the given string.

Example

Private Sub Constant_demo_Click()
   Dim var1 as Variant
   
   var1 = "Microsoft"
   Dim var2 as Variant
   
   var2 = "VBScript"
   msgbox(var1 & Space(2)& var2)
End Sub
When you execute the above function, it produces the following output.
Microsoft VBScript

VBA - strComp

The StrComp function returns an integer value after comparing the two given strings. It can return any of the three values -1, 0, or 1 based on the input strings to be compared.
  • If String 1 < String 2, then StrComp returns -1
  • If String 1 = String 2, then StrComp returns 0
  • If String 1 > String 2, then StrComp returns 1

Syntax

StrComp(string1,string2[,compare]) 

Parameter Description

  • String1 − A required parameter. The first string expression.
  • String2 − A required parameter. The second string expression.
  • Compare − An optional parameter. Specifies the string comparison to be used. It can take the following values.
    • 0 = vbBinaryCompare - Performs Binary Comparison(Default)
    • 1 = vbTextCompare - Performs Text Comparison

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   Dim var1 as Variant
   msgbox("Line 1 :" & StrComp("Microsoft","Microsoft"))
   msgbox("Line 2 :" &StrComp("Microsoft","MICROSOFT"))
   msgbox("Line 3 :" &StrComp("Microsoft","MiCrOsOfT"))
   msgbox("Line 4 :" &StrComp("Microsoft","MiCrOsOfT",1))
   msgbox("Line 5 :" &StrComp("Microsoft","MiCrOsOfT",0))
End Sub
When you execute the above function, it produces the following output.
Line 1 :0
Line 2 :1
Line 3 :1
Line 4 :0
Line 5 :1 


VBA - String Function

The String function fills a string with the specified character for specified number of times.

Syntax

String(number,character)

Parameter Description

  • Number − A required parameter. An integer value, which would be repeated for a specified number of times against the character parameter.
  • Character − A required parameter. Character value, which has to be repeated for a specified number of times.

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   msgbox("Line 1 :" & String(3,"$"))
   msgbox("Line 2 :" & String(4,"*"))
   msgbox("Line 3 :" & String(5,100))
   msgbox("Line 4 :" & String(6,"ABCDE"))
End Sub
When you execute the above function, it produces the following output.
Line 1 :$$$
Line 2 :****
Line 3 :ddddd
Line 4 :AAAAAA


VBA - String Reverse Function

The StrReverse function reverses the specified string.

Syntax

StrReverse(string) 

Example

Add a button and add the following function.
Private Sub Constant_demo_Click()
   msgbox("Line 1 : " & StrReverse("VBSCRIPT"))
   msgbox("Line 2 : " & StrReverse("My First VBScript"))
   msgbox("Line 3 : " & StrReverse("123.45"))
End Sub
When you execute the above function, it produces the following output.
Line 1 : TPIRCSBV
Line 2 : tpircSBV tsriF yM
Line 3 : 54.321

Comments

Blogger
Name

.NET_Interview,1,Accenture,1,Accenture News,1,Accenture_GFT,1,Accenture_Prep,1,Advance_Excel,22,Advance_Python,10,Advanced_Linux,6,Advanced_SQL,18,Advanced_Unix,6,AI,1,Alexa,1,Amazon,1,Amazon News,5,AMCAT,1,AMCAT_Prep,1,AMCAT_Solved_Papers,1,Ancient India,5,Android,1,Ansible,2,Apache_Sqoop,10,Aptitude,1,AWS,23,AWS CLI,6,AWS DeepRacer,1,AWS Tutorials,13,AWS_Dumps,1,AWS_Interview,1,AZ-104,1,AZ-900,2,Azure,3,Azure Administrator Associate,1,B_Tech,19,B.Tech,1,B.Tech Jobs,1,Backup,2,Banking Exam,1,Banking_Exam,1,Basic_Linux,29,Basic_Python,19,Basic_SQL,24,Basic_Unix,30,Best_Websites,1,Big_Data_Analytics,70,Blog,447,Blogger,3,Blogging,2,Blogspot,1,Books,2,BTech,20,C++_Interview,1,CBSE,93,Certification,5,Cheat Sheet,2,Civil_1st_Semester,1,Class 11,2,Class 11 Physics,2,Class 12,61,Class 12 Biology,16,Class 12 Chemistry,16,Class 12 English,14,Class 12 Physics,15,Class_12,28,Class_12_Chemistry,4,Class_12_Computer_Science,7,Class_12_Mathematics,1,Class_12_NCERT,15,Class_12_NCERT_Solutions,15,Class_12_Physics,18,Class_12_Physics_NCERT_Solutions,15,Class_12_Science,29,Cloud,2,Cloud_Service,1,CloudFormation,2,Coding,1,Cognizant News,1,Communication,2,Computer,18,Computer_Memory,2,Computer_Programming,2,Computer_Science,4,Control_System,9,Crack_Interview,3,CSE_5th_Semester,1,CSS,1,Data Analyst Jobs,1,Data Science,2,Data Science Interview,1,Data_Analytics,16,Data_Science,18,Data_Science_Interview,1,Database,47,Database Interview,2,Database_Interview,4,DP-900,1,Dumps,2,ECE_1st_Semester,1,ECE_1st_Year,1,ECE_4th_Semester,9,Electrical,2,Electrical_1st_Semester,1,Electronics,2,Electronics & Communication,1,Electronics_&_Communication,14,English,2,Error,1,Ethical Hacking,2,Ethical_Hacking,1,ETL_Tools,17,Exam Dumps,1,Exam Preparation,13,Exam_Cracker,3,Exams,5,Exams_Banking,1,Exams_Prep,1,Excel,22,Excel_Macros,22,Excel_Terms,1,Excel_VBA,22,File System,1,Free_OS,1,Free_Softwares,1,Games,3,GATE,20,GATE EC,1,GATE Electronics & Communication,1,GATE_2019,16,GATE_2020,16,GATE_2021,7,GATE_EC,10,GATE_ECE,9,GATE_ECE_Best_Book,1,GATE_Electrical,1,GATE_Electronics,12,GATE_Made_Easy,1,GATE_ME,6,GATE_Mechanical,6,GGSIPU,1,Google,1,Google Cloud,2,Google News,2,Government Jobs,2,Graphic,1,GRUB,1,Handwritten_Notes,10,Hardware,6,HCL_Prep,1,HDFS,1,Hive,1,Hive Tutorials,1,Hosting,2,How To,15,How_To,8,HR Interview,2,HR Interview Questions,1,HR_Interview,3,Hyderabad_News,1,IBPS,2,IBPS_English,1,IBPS_PO,2,Indian History,5,Informatica,1,Informatica_Interview,1,Information,20,Internet,5,Interview,20,Interview Preparation,5,Interview_Prep,20,IPU,1,ISRO Jobs,1,IT Jobs,4,IT News,1,Java,2,Java Interview,1,Java_Interview,2,Java_Questions_&_answers,1,JavaScript,1,JEE,6,JEE_Mains,6,Job Alert,6,Jobs,1,Kali Linux,1,Kali Linux Tools,1,Katoolin,1,Keyboard,1,Keyboard_Shortcuts,1,Learn_VBA,22,Linux,42,Linux Command,1,Linux Interview,1,Linux Mint,1,Linux Tools,4,Linux Tutorials,1,Linux_Distributions,1,Linux_Interview,1,Linux_Redirections,1,Linux_Scripting,30,Linux_Shell_Arrays,1,Linux_Shell_Functions,1,Linux_Shell_Quote,1,Linux_Signals_And_Traps,1,Logical_Reasoning,1,M.Tech Jobs,1,Machine Learning,1,Machine Learning Interview,1,Machine_Learning,1,Machine_Learning_Interview,1,Macros,22,Manufacturing_Processes,1,ME_1st_Semester,1,ME_Fluid_Mechanics,1,ME_Industrial_Engineering,1,ME_Machine_Design,1,Mechanical,6,Memory,1,Microcontroller,1,Microsoft,5,Microsoft Azure Associate,1,Microsoft Azure Fundamentals,3,Microsoft_Azure,1,Microsoft_Azure_Interview,1,Mobile,1,Mobile News,1,MS_Access,40,MySQL,44,NCERT Solutions,63,Network,2,News,3,Notes,31,OOPs,1,Open_Source_OS,1,OpenTelemetry,1,Operating_Systems,2,Operating_Systems_Interview,1,Oracle,42,Oracle Interview,1,Oracle_Interview,1,Paytm Jobs,1,Physics,2,PL_SQL,42,PL_SQL_Interview,1,Placement,21,Placement Preparation,18,Placement_Prep,24,Poetry,1,Programming,28,Programming_Languages,1,Python,28,Python_Built_In_Strings_Methods,2,Python_built_In_Tuple_Functions,1,Python_CAlling_a_Function,1,Python_CGI,1,Python_Class,1,Python_Data_Types,1,Python_DAte,1,Python_Decision_Making,1,Python_Dictionary,1,Python_DOM_APIs,1,Python_Features,1,Python_Files_Functions,1,Python_For_Loop,1,Python_Functions,6,Python_GUI,1,Python_History,1,Python_If_Else,1,Python_import_Statements,1,Python_Installation,1,Python_Interview,1,Python_JPython,1,Python_Lists,2,Python_Loops,1,Python_Methods,1,Python_Modules,1,Python_MySQL,1,Python_Nested_If_Else,1,Python_Nested_Loops,1,Python_Number_Type_Conversion,1,Python_Numbers,2,Python_Object_Oriented,1,Python_OOP,1,Python_Pass_By_Reference_vs_Value,1,Python_Programming,28,Python_Scripting,28,Python_Special_Operators,1,Python_Strings,2,Python_Strings_Functions,1,Python_Threading_Module,1,Python_Time,1,Python_Tkinter,1,Python_Tuples,2,Python_Tutorial,28,Python_Types_of_Loops,1,Python_Variables,1,Python_Web_Server,1,Python_While_Loop,1,Python_wxPython,1,Python_XML_Processing,1,PythonPath_Setup,1,Quantitative,1,Quantitative_Aptitude,2,RDBMS,1,Run,1,S3,1,Sabrent,1,Samsung,1,SBI Jobs,1,Scripting,52,Scripting Interview,1,Security,1,Server,2,service now,1,Shell_Command_Manual,1,Shell_Logging_Commands,1,Shell_Scripting,31,Shell_Scripting_Interview,1,Software Engineering Interview,1,Software_Engineering_Interview,1,Solutions,1,Spinnaker,1,SQL,46,SQL Interview,1,SQL_Alias_Syntax,1,SQL_Alter_Table_Query,1,SQL_Alter_Table_Statement,1,SQL_AND_OR_Query,1,SQL_AND_OR_Statement,1,SQL_Architecture,1,SQL_Clone_Table,1,SQL_Commands,1,SQL_Conjunctive_Operators,1,SQL_Constraints,1,SQL_Create_Database,1,SQL_Create_Table,1,SQL_DataTypes,1,SQL_Date_Functions,1,SQL_Date_Statement,1,SQL_DCL,1,SQL_Server,39,SQL_Temporary_Table_Statement,1,SQLite,43,Sqoop,9,Sqoop_Tutorial,10,SSC,4,SSC CGL,1,SSC CHSL,1,SSC_CGL,3,SSC_CGL_English,1,SSC_CHSL,1,SSC_CPO,1,SSC_GS,1,SSC_Quantative,1,SSD,1,ssl,1,Storage,1,Talend,17,Talend Interview,1,Talend_ETL,15,Talend_Tutorials,16,TCS Interview,1,TCS Jobs,1,TCS News,1,TCS_Interview,1,TCS_Prep,1,Tech News,15,Tech Tips,18,Teradata_Interview,1,Terraform,2,Tips & Tricks,12,Tips_&_Tricks,18,Top 10,3,Top 50,10,Top_10,1,Top_50,18,Top25,1,Tutorials,165,Tutorials_Python,28,Tutorials_VBA,11,Unix,32,Unix Interview,1,Unix Tutorials,1,Unix_Interview,1,Unix_Scripting,31,UPSC,5,VBA,22,VBA_Basics,22,VBA_Excel,22,VBA_Scripting,22,VBA_Tutorials,22,VirtualBox,1,Visual_Basic_Application,22,VPN,1,Websites,3,Windows,22,WordPress,2,Yarn,1,
ltr
item
Shout4Education - Get Jobs, Tutorials and Notes: VBA - Strings
VBA - Strings
VBA - Strings - VBA Tutorials ... Best VBA Tutorials only @ Shout4Education ... Learn VBA in an Easy and Simplified Way ... Keep Shouting For Education and Keep Learning ... VBA - Strings Shout4Education Strings are a sequence of characters, which can consist of either alphabets, numbers, special characters, or all of them. A variable is said to be a string if it is enclosed within double quotes " ". Syntax variablename = "string" Examples str1 = "string" ' Only Alphabets str2 = "132.45" ' Only Numbers str3 = "!@#$;*" ' Only Special Characters Str4 = "Asc23@#" ' Has all the above String Functions There are predefined VBA String functions, which help the developers to work with the strings very effectively. Following are String methods that are supported in VBA. Please click on each one of the methods to know in detail. Sr.No. Function Name & Description 1 InStr Returns the first occurrence of the specified substring. Search happens from the left to the right. 2 InstrRev Returns the first occurrence of the specified substring. Search happens from the right to the left. 3 Lcase Returns the lower case of the specified string. 4 Ucase Returns the upper case of the specified string. 5 Left Returns a specific number of characters from the left side of the string. 6 Right Returns a specific number of characters from the right side of the string. 7 Mid Returns a specific number of characters from a string based on the specified parameters. 8 Ltrim Returns a string after removing the spaces on the left side of the specified string. 9 Rtrim Returns a string after removing the spaces on the right side of the specified string. 10 Trim Returns a string value after removing both the leading and the trailing blank spaces. 11 Len Returns the length of the given string. 12 Replace Returns a string after replacing a string with another string. 13 Space Fills a string with the specified number of spaces. 14 StrComp Returns an integer value after comparing the two specified strings. 15 String Returns a string with a specified character for specified number of times. 16 StrReverse Returns a string after reversing the sequence of the characters of the given string. VBA - Instr The InStr Function returns the first occurrence of one string within another string. The search happens from the left to the right. Syntax InStr([start,]string1,string2[,compare]) Parameter Description Start − An optional parameter. Specifies the starting position for the search. The search begins at the first position from the left to the right. String1 − A required parameter. String to be searched. String2 − A required parameter. String against which String1 is searched. Compare − An optional parameter. Specifies the string comparison to be used. It can take the following mentioned values. 0 = vbBinaryCompare - Performs Binary Comparison (Default) 1 = vbTextCompare - Performs Text Comparison Example Add a button and add the following function. Private Sub Constant_demo_Click() Dim Var As Variant Var = "Microsoft VBScript" MsgBox ("Line 1 : " & InStr(1, Var, "s")) MsgBox ("Line 2 : " & InStr(7, Var, "s")) MsgBox ("Line 3 : " & InStr(1, Var, "f", 1)) MsgBox ("Line 4 : " & InStr(1, Var, "t", 0)) MsgBox ("Line 5 : " & InStr(1, Var, "i")) MsgBox ("Line 6 : " & InStr(7, Var, "i")) MsgBox ("Line 7 : " & InStr(Var, "VB")) End Sub When you execute the above function, it produces the following output. Line 1 : 6 Line 2 : 0 Line 3 : 8 Line 4 : 9 Line 5 : 2 Line 6 : 16 Line 7 : 11 VBA -InString Reverse The InStrRev function returns the first occurrence of one string within another string. The Search happens from the right to the left. Syntax InStrRev(string1,string2[,start,[compare]]) Parameter Description String1 − A required parameter. String to be searched. String2 − A required parameter. String against which String1 is searched. Start − An optional parameter. Specifies the starting position for the search. The search begins at the first position from the right to the left. Compare − An optional parameter. Specifies the string comparison to be used. It can take the following mentioned values. 0 = vbBinaryCompare - Performs Binary Comparison (Default) 1 = vbTextCompare - Performs Text Comparison Example Add a button and place the following function. Private Sub Constant_demo_Click() var = "Microsoft VBScript" msgbox("Line 1 : " & InStrRev(var,"s",10)) msgbox("Line 2 : " & InStrRev(var,"s",7)) msgbox("Line 3 : " & InStrRev(var,"f",-1,1)) msgbox("Line 4 : " & InStrRev(var,"t",5)) msgbox("Line 5 : " & InStrRev(var,"i",7)) msgbox("Line 6 : " & InStrRev(var,"i",7)) msgbox("Line 7 : " & InStrRev(var,"VB",1)) End Sub Upon executing the above script, it produces the following result. Line 1 : 6 Line 2 : 6 Line 3 : 8 Line 4 : 0 Line 5 : 2 Line 6 : 2 Line 7 : 0 VBA - Lcase The LCase function returns the string after converting the entered string into lower case letters. Syntax Lcase(String) Example Add a button and place the following function inside the same. Private Sub Constant_demo_Click() var = "Microsoft VBScript" msgbox("Line 1 : " & LCase(var)) var = "MS VBSCRIPT" msgbox("Line 2 : " & LCase(var)) var = "microsoft" msgbox("Line 3 : " & LCase(var)) End Sub Upon executing the above script, it produces the following output. Line 1 : microsoft vbscript Line 2 : ms vbscript Line 3 : microsoft VBA - UCase The UCase function returns the string after converting the entered string into UPPER case letters. Syntax UCase(String) Example Add a button and place the following function inside the same. Private Sub Constant_demo_Click() var = "Microsoft VBScript" msgbox("Line 1 : " & UCase(var)) var = "MS VBSCRIPT" msgbox("Line 2 : " & UCase(var)) var = "microsoft" msgbox("Line 3 : " & UCase(var)) End Sub Upon executing the above script, it produces the following output. Line 1 : MICROSOFT VBSCRIPT Line 2 : MS VBSCRIPT Line 3 : MICROSOFT VBA - Left The Left function returns a specified number of characters from the left side of the given input string. Syntax Left(String, Length) Parameter Description String − A required parameter. Input String from which the specified number of characters to be returned from the left side. Length − A required parameter. An Integer, which specifies the number of characters to be returned. Example Add a button and add the following function. Private Sub Constant_demo_Click() Dim var as Variant var = "Microsoft VBScript" msgbox("Line 1 : " & Left(var,2)) var = "MS VBSCRIPT" msgbox("Line 2 : " & Left(var,5)) var = "microsoft" msgbox("Line 3 : " & Left(var,9)) End Sub When you execute the above function, it produces the following output. Line 1 : Mi Line 2 : MS VB Line 3 : microsoft VBA - Right The Right function returns a specified number of characters from the right side of the given input string. Syntax Right(String, Length) Parameter Description String − A required parameter. Input String from which the specified number of characters to be returned from the right side. Length − A required parameter. An Integer, which Specifies the number of characters to be returned. Example Add a button and add the following function. Private Sub Constant_demo_Click() var = "Microsoft VBScript" msgbox("Line 1 : " & Right(var,2)) var = "MS VBSCRIPT" msgbox("Line 2 : " & Right(var,5)) var = "microsoft" msgbox("Line 3 : " & Right(var,9)) End Sub When you execute the above function, it produces the following output. Line 1 : pt Line 2 : CRIPT Line 3 : microsoft VBA - Mid The Mid Function returns a specified number of characters from a given input string. Syntax Mid(String,start[,Length]) Parameter Description String − A required parameter. Input String from which the specified number of characters to be returned. Start − A required parameter. An Integer, which specifies the starting position of the string. Length − An optional parameter. An Integer, which specifies the number of characters to be returned. Add a button and add the following function. Private Sub Constant_demo_Click() Dim var as Variant var = "Microsoft VBScript" msgbox("Line 1 : " & Mid(var,2)) msgbox("Line 2 : " & Mid(var,2,5)) msgbox("Line 3 : " & Mid(var,5,7)) End Sub When you execute the above function, it produces the following output. Line 1 : icrosoft VBScript Line 2 : icros Line 3 : osoft V VBA - Ltrim The Ltrim function removes the blank spaces from the left side of the string. Syntax LTrim(String) Example Add a button and add the following function. Private Sub Constant_demo_Click() Dim var as Variant var = " Microsoft VBScript" msgbox "After Ltrim : " & LTrim(var) End Sub When you execute the function, it produces the following output. After Ltrim : Microsoft VBScript VBA - Rtrim The Rtrim function removes the blank spaces from the right side of the string. Syntax RTrim(String) Example Add a button and add the following function. Private Sub Constant_demo_Click() Dim var as Variant var = "Microsoft VBScript " msgbox("After Rtrim : " & RTrim(var)) End Sub When you execute the above function, it produces the following output. After Rtrim : Microsoft VBScript VBA - Trim The Trim function removes both the leading and the trailing blank spaces of the given input string. Syntax Trim(String) Example Add a button and add the following function. Private Sub Constant_demo_Click() var = "Microsoft VBScript" var = " Microsoft VBScript " msgbox ("After Trim : " & Trim(var)) End Sub When you execute the above function, it produces the following output. After trim : Microsoft VBScript VBA - Len The Len function returns the length of the given input string including the blank spaces. Syntax Len(String) Example Add a button and add the following function. Private Sub Constant_demo_Click() Dim var1 as Variant Dim var2 as Variant var1 ="Microsoft VBScript" msgbox("Length of var1 is : " & Len(var1)) var2 = " Microsoft VBScript " msgbox ("Length of var2 is : " & Len(var2)) End Sub When you execute the above function, it produces the following output. Length of var1 is : 18 Length of var2 is : 36 VBA - Replace The Replace function replaces a specified part of a string with a specific string, a specified number of times. Syntax Replace(string,find,replacewith[,start[,count[,compare]]]) Parameter Description String − A required parameter. The Input String which is to be searched for replacing. Find − A required parameter. The part of the string that will be replaced. Replacewith − A required parameter. The replacement string, which would be replaced against the find parameter. Start − An optional parameter. Specifies the start position from where the string has to be searched and replaced. Default value is 1. Count − An optional parameter. Specifies the number of times the replacement has to be performed. Compare − An optional parameter. Specifies the comparison method to be used. Default value is 0. 0 = vbBinaryCompare - Performs a binary comparison 1 = vbTextCompare - Performs a Textual comparison Example Private Sub Constant_demo_Click() Dim var as Variant var = "This is VBScript Programming" 'VBScript to be replaced by MS VBScript msgbox("Line 1: " & Replace(var,"VBScript","MS VBScript")) 'VB to be replaced by vb msgbox("Line 2: " & Replace(var,"VB","vb")) ''is' replaced by ## msgbox("Line 3: " & Replace(var,"is","##")) ''is' replaced by ## ignores the characters before the first occurence msgbox("Line 4: " & Replace(var,"is","##",5)) ''s' is replaced by ## for the next 2 occurences. msgbox("Line 5: " & Replace(var,"s","##",1,2)) ''r' is replaced by ## for all occurences textual comparison. msgbox("Line 6: " & Replace(var,"r","##",1,-1,1)) ''t' is replaced by ## for all occurences Binary comparison msgbox("Line 7: " & Replace(var,"t","##",1,-1,0)) End Sub When you execute the above function, it produces the following output. Line 1: This is MS VBScript Programming Line 2: This is vbScript Programming Line 3: Th## ## VBScript Programming Line 4: ## VBScript Programming Line 5: Thi## i## VBScript Programming Line 6: This is VBSc##ipt P##og##amming Line 7: This is VBScrip## Programming VBA - Space The Space function fills a string with a specific number of spaces. Syntax space(number) Parameter Description Number − A required parameter. The number of spaces that we want to add to the given string. Example Private Sub Constant_demo_Click() Dim var1 as Variant var1 = "Microsoft" Dim var2 as Variant var2 = "VBScript" msgbox(var1 & Space(2)& var2) End Sub When you execute the above function, it produces the following output. Microsoft VBScript VBA - strComp The StrComp function returns an integer value after comparing the two given strings. It can return any of the three values -1, 0, or 1 based on the input strings to be compared. If String 1 < String 2, then StrComp returns -1 If String 1 = String 2, then StrComp returns 0 If String 1 > String 2, then StrComp returns 1 Syntax StrComp(string1,string2[,compare]) Parameter Description String1 − A required parameter. The first string expression. String2 − A required parameter. The second string expression. Compare − An optional parameter. Specifies the string comparison to be used. It can take the following values. 0 = vbBinaryCompare - Performs Binary Comparison(Default) 1 = vbTextCompare - Performs Text Comparison Example Add a button and add the following function. Private Sub Constant_demo_Click() Dim var1 as Variant msgbox("Line 1 :" & StrComp("Microsoft","Microsoft")) msgbox("Line 2 :" &StrComp("Microsoft","MICROSOFT")) msgbox("Line 3 :" &StrComp("Microsoft","MiCrOsOfT")) msgbox("Line 4 :" &StrComp("Microsoft","MiCrOsOfT",1)) msgbox("Line 5 :" &StrComp("Microsoft","MiCrOsOfT",0)) End Sub When you execute the above function, it produces the following output. Line 1 :0 Line 2 :1 Line 3 :1 Line 4 :0 Line 5 :1 VBA - String Function The String function fills a string with the specified character for specified number of times. Syntax String(number,character) Parameter Description Number − A required parameter. An integer value, which would be repeated for a specified number of times against the character parameter. Character − A required parameter. Character value, which has to be repeated for a specified number of times. Example Add a button and add the following function. Private Sub Constant_demo_Click() msgbox("Line 1 :" & String(3,"$")) msgbox("Line 2 :" & String(4,"*")) msgbox("Line 3 :" & String(5,100)) msgbox("Line 4 :" & String(6,"ABCDE")) End Sub When you execute the above function, it produces the following output. Line 1 :$$$ Line 2 :**** Line 3 :ddddd Line 4 :AAAAAA VBA - String Reverse Function The StrReverse function reverses the specified string. Syntax StrReverse(string) Example Add a button and add the following function. Private Sub Constant_demo_Click() msgbox("Line 1 : " & StrReverse("VBSCRIPT")) msgbox("Line 2 : " & StrReverse("My First VBScript")) msgbox("Line 3 : " & StrReverse("123.45")) End Sub When you execute the above function, it produces the following output. Line 1 : TPIRCSBV Line 2 : tpircSBV tsriF yM Line 3 : 54.321 @ Shout 4 Education , @ Shout For Education
https://1.bp.blogspot.com/-6xBCiWeXIsc/XoZMSCtu_6I/AAAAAAAAA40/L-2muAnBbkEdiOgs7Ja3hJoFd8-3ofuuQCPcBGAYYCw/s640/VBA_Tutorials_Shout4Education.jpg
https://1.bp.blogspot.com/-6xBCiWeXIsc/XoZMSCtu_6I/AAAAAAAAA40/L-2muAnBbkEdiOgs7Ja3hJoFd8-3ofuuQCPcBGAYYCw/s72-c/VBA_Tutorials_Shout4Education.jpg
Shout4Education - Get Jobs, Tutorials and Notes
https://www.shout4education.com/2020/04/vba-strings.html
https://www.shout4education.com/
https://www.shout4education.com/
https://www.shout4education.com/2020/04/vba-strings.html
true
7947974353386595563
UTF-8
Loaded All Posts Not Found Any Posts :( View All Read More Reply Cancel Reply Delete By Home Pages Posts View All Similar Posts Label Archive Search All Posts Not Found Any Post Match with Your Request Sorry !! Search Something Blazing :) Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Just Now 1 Minute Ago $$1$$ minutes ago 1 Hour Ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago More than 5 Weeks Ago Followers Follow :) This Premium Content is LOCKED !!! STEP 1: Share. STEP 2: Click the Link You Shared to Unlock Copy All Code Select All Code All Codes were Copied to Your Clipboard :) Can NOT Copy the Codes / Texts, Please Press [CTRL]+[C] (or CMD+C with Mac) to Copy