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 (, n)

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 sub-string 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

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest