String manipulation is very important in any programing language. It is same for VBA as well. In this post let’s learn an another very important string manipulation technique. So today’s lesson is about how to remove characters from left or right of a string.
Let’s assume we have a string type variable call “MyString”.
|
Dim MyString As String MyString = "abcde123" |
This string has 8 characters. So if we want to remove 3 characters from right side how do we do that? We can simply use left function to do the job. Here is how.
|
Dim MyString As String Dim NewString As String MyString = "abcde123" NewString = Left(MyString, 5) Debug.Print NewString |
MyString has 8 characters. If we want to remove 3 from right, that means we need first 5 characters from left. So if you run above code, you will see the value of NewString in the immediate window as follows.
Similarly if we want to remove characters from left then we can use right function for that. Following code shows how you can remove first 2 characters from left.
|
Dim MyString As String Dim NewString As String MyString = "abcde123" NewString = Right(MyString, 6) Debug.Print NewString |
If you run above code you will see this result in immediate window.
So what if, if you want to remove characters from variable length string. Then we can’t use constant value with left or right functions. But we can solve this with “Len” function. We can calculate the length of the string using “Len” function. Then we can subtract amount of characters we want to remove. Following code will remove first 2 characters from right side of a string.
|
Dim MyString As String Dim NewString As String MyString = "abcde123" NewString = Left(MyString, Len(MyString) - 2) Debug.Print NewString |
This is the result you will get.
And following example shows how to remove first 3 characters from left of a variable length string.
| Dim MyString As String Dim NewString As String MyString = "abcde123" NewString = Right(MyString, Len(MyString) - 3) Debug.Print NewString |
And you will see this result in immediate window.
