VBA Programming : String Manipulation
Many a times, we come across to deal with Strings. Like Length of the String, Concatenating two or more Strings etc. This article teaches you how to deal with strings with the following different operations:
1. Concatenating Strings (Joining Strings)
2. Finding Length of a String (Len Function)
3. Remove Spaces from a String (Trim Function)
4. Left Function
5. Right Function
6. MID Function
7. InStr Function
8. Chr Function
9. Asc Function
Concatenating Strings (Join Strings) :
In Excel VBA, two strings can be Joined simply by adding them by using & sign. Refer the below example.
Private Sub CommandButton1_Click() Dim Strng1, Strng2, Strng3, RsltStrng As String Strng1 = "My Name " Strng2 = "Is " Strng3 = "Vish" RsltStrng = Strng1 & Strng2 & Strng3 MsgBox (RsltStrng) End Sub
Result:
My Name Is Vish
Len Function
This Function is basically used to get the length of the String (Count of All letters including Spaces of a String).
It returns an Integer Value.
Syntax:
=Len(String)
Example
=Len (Vishwa) = 6 and =Len (Learn Excel Macro) = 17
Right Function
The Right function extracts the right part of a String.
Syntax:
Right (
Where:
Str : Is the Original String.
n : Number of Character to be extracted from Right of the Original String
Example:
Right(“Learn Excel Macro”, 5) = Macro
Left Function
The Left function extract the left part of a String.
Syntax:
Left (Str, n)
Where:
Str : Is the Original String.
n : Number of Character to be extracted from Left of the Original String
Example:
Left(“Learn Excel Macro”, 5) = Learn
Ltrim Function
The Ltrim function trims the empty spaces of the left portion of the String.
Syntax:
=Ltrim(String)
Example:
Ltrim (” Learn Excel Macro “)= Learn Excel Macro
Rtrim Function
The Rtrim function trims the empty spaces of the right part of the String.
Syntax:
=Rtrim(String)
Example:
Rtrim (” Learn Excel Macro “) = Learn Excel Macro
Trim function
Trim Function is combination of both RTrim and LTrim. It means, it removes all the spaces from Left and Right both the sides.
Syntax:
=Trim(String)
Example:
Trim (“ Learn Excel Macro ”) = Learn Excel Macro
Mid Function
The Mid function extracts a substring from the original phrase or string. It takes the following format:
Syntax:
Mid(string, position, n)
Where:
String : The Original String
Position : Starting position from where extraction should start
n : Number of Characters from the Start position
Example:
Mid(“Learn Excel Macro”, 3, 5) = rn Ex
InStr function
The InStr function looks for a phrase or string that is embedded within the original phrase and returns the starting position of the embedded phrase. Basically this is a search function within a string.
Syntax:
Instr (n, String, Search)
Where:
n : Place from where Search Phrase will be started searching
String : Original String where search string will be searched
Search : It is a search String which is searched in the Original String
Example:
Instr(1, “Learn Excel Macro, “Excel”)=7
Note:It returns the position number where that Search String is found. In the above example, Excel word is found from 7th position of the Original String. That is why it returns 7.
Ucase and the Lcase functions
The Ucase function converts all the characters of a string to capital letters. On the other hand, the Lcase function converts all the characters of a string to small letters.
Example:
Ucase(“Learn Excel Macro”) =LEARN EXCEL MACRO
Lcase(“Learn Excel Macro”) =learn excel macro
Str and Val functions
The Str is the function that converts a number to a string while the Val function converts a string to a number. The two functions are important when we need to perform mathematical operations.
Chr and the Asc functions
The Chr function returns the string that corresponds to an ASCII code while the Asc function converts an ASCII character or symbol to the corresponding ASCII code. There are 255 ASCII codes and as many ASCII characters.
The format of the Chr function is
Chr(charcode)
and the format of the Asc function is
Asc(Character)
Example:
Chr(65)=A
Chr(122)=z
Asc(“B”)=66
To Check out more Excel Macro Tutorials, visit Excel Macro Tutorial 
My name is Vishwamitra Mishra. Friends Call me Vishwa. This blog is authored by me. I am an Excel Geek. Well, this blog talks a lot about my passion in Excels & Macros so I’ll not talk about it :) I am very much passionate about traveling & quite recently discovered that I am a good photographer too..:P

SUBSCRIBEAND GET A FREE!EBOOK FOR EXCEL VBA BEGINNERSDON'T MISS ANY NEW ARTICLE ! 
Get your FREE! EBook & Have Every New Article Delivered Straight To Your EmailBox 