Excel VBA Programming
Home
Getting Started
8 part section >>
VBA Programming Variables
6 Part Section >>
Conditional Logic
9 part section >>
Strings and String Functions
Text Strings in Excel VBA
Changing Case
Trim, Len, and Space
Excel VBA Replace
InStr, InStrRev, StrReverse
The Left and Right Functions
The Mid Function
A String Exercise
Programming Loops
4 part section >>
Programming Arrays
4 part section >>
Subs and Functions
6 part section >>
Excel VBA and Text Files
2 part section >>
Excel VBA and User Forms
5 part section >>
An Excel Picture Viewer Project
12 part section >>
Excel VBA and Charts
4 part section >>
A TreeView Project
A 4 part section >>
> BUY THE BOOK OF THIS COURSE <
Working with Strings of Text in Excel VBA
One variable type that we haven't touched upon yet is the As String type. As its name suggest, it
is used to hold strings of text. You'll need to work with strings of text quite a lot in Excel VBA, so it's
well worth getting the hang of.
Setting up a variable to hold text is quite straightforward. You simply Dim a variable As String:
Dim MyString As String
To store text inside of your variable you need to surround it with double quotes:
MyString = "Some text"
Even if you place numbers between double quotes they still gets treated as text and not Integers:
MyString = "25"
The above line means store 25 as text, and NOT store the number 25.
You can place text into a cell on your spreadsheet:
Dim MyString As String
MyString = "Some text"
1
[Link] = MyString
And you can get text out of cell on your spreadsheet:
Dim MyString As String
MyString = [Link]
Quite often, though, you'll need to do something with the text that you get from a cell on a
spreadsheet. For example, you may need to take a full name from one cell and place the first
name in another cell and the surname in yet another. To do things like this, you need to know how
to use Excel VBA's builtin string functions. The functions we'll study are these:
LCase, UCase
Trim and Len
Space
Replace
StrReverse
InStr, InStrRev
Left, Right
Mid
As you can see, there are quite a lot of them. And this is not even a full list!
In the next few lessons, we'll take a look at the above list of methods and how to use them in your
Excel VBA code. First up is changing case.
Change Case Methods >
Lots more free online course here on our main Home and Learn site
© All course material copyright Ken Carney
2
Excel VBA Programming
Home
Getting Started
8 part section >>
VBA Programming Variables
6 Part Section >>
Conditional Logic
9 part section >>
Strings and String Functions
Text Strings in Excel VBA
Changing Case
Trim, Len, and Space
Excel VBA Replace
InStr, InStrRev, StrReverse
The Left and Right Functions
The Mid Function
A String Exercise
Programming Loops
4 part section >>
Programming Arrays
4 part section >>
Subs and Functions
6 part section >>
Excel VBA and Text Files
2 part section >>
Excel VBA and User Forms
5 part section >>
An Excel Picture Viewer Project
12 part section >>
Excel VBA and Charts
4 part section >>
A TreeView Project
A 4 part section >>
> BUY THE BOOK OF THIS COURSE <
LCase and UCase in Excel VBA
Two of the easier inbuilt functions to get the hang of are LCase and UCase. As you might expect,
these are used to changes letters into lowercase or uppercase characters. Let's see how they
work.
To try these out, create a new blank workbook in Excel. Save the file as string_functions.xlsm.
Enter some headings in cells A1, B1, and C1. Enter: Text, LCase, and UCase. Enter a name in cell
A2, anything you like. Your spreadsheet might then look like this:
Click the Developer ribbon at the top of Excel, then click the View Code item on the Controls
panel.
In the VBA Editor, if you can't see a blank code window for Sheet1, double click Sheet1 in the
Project Explorer on the left. (If you can't see the Project Explorer, click View > Project Explorer
3
from the menu at the top.)
Create a new sub in your blank Sheet1 coding window. Call it ChangeCase. To get the text out of
cell A2 on your spreadsheet, add the following lines:
Dim FullName As String
FullName = Range("A2").Value
All this code does is to set up a variable called FullName. The variable is declared with As String.
To place something into this variable, we've used Range("A2").Value.
Your code should now look like this:
To use the lowercase function, you only need the following on the right of an equal sign:
LCase(Text_To_Convert)
Whatever you're trying to convert goes between the round brackets of the LCase function. The text
you're trying to convert can be direct text surrounded by double quotes, or a variable that holds
some a string of text.
We want to place the converted text in cell B2, just under the LCase heading. All we need to do is
use offset with our A2 Range:
Range("A2").Offset(, 1).Value = LCase(FullName)
Range("A2").Offset(, 1) moves us one column to the right of cell A2. We then access the Value
property. To the right of the equal sign we have out LCase function. VBA will convert whatever we
have in the variable called FullName to lowercase, and use that as the Value for cell B2.
Add the line to your own Sub and your code will look like this:
Click anywhere inside of your Sub to try it out. Now press F5 on your keyboard to run the code.
Switch to your spreadsheet and you should see this:
The code to convert the name to uppercase is very similar. It's this:
Range("A2").Offset(, 2).Value = UCase(FullName)
Only two things have change, here. For the Offset, we have a 2 instead of a 1. This moves us two
columns to the right of cell A2. The function that converts to uppercase is UCase. It's used in
exactly the same way as LCase.
Add the line to your own code. Run your Sub and your spreadsheet will look like this:
So we have now converted the name in cell A2 to lowercase and uppercase. Notice that the name
in A2, David Gilmour, is in Proper Case. This is when you capitalise the first letter of each word.
Sadly, Excel VBA doesn't have a nice, easy function to convert to Proper Case, so there's no
PCase.
4
There is, however, a Worksheet function called Proper. To use it, try the following code:
Dim FullName As String
FullName = "DAVID GILMOUR"
Range("A2").Offset(, 3).Value = [Link](FullName)
The code that converts to Proper Case is this:
[Link](FullName)
Application is a toplevel object, meaning the whole of Excel. WorksheetFunction is used to
access Excel's inbuilt function. One of these functions is Proper. In between the round brackets of
Proper, you type the variable you're trying to convert. As well as typing a variable name, you can
type direct text surrounded by double quotes.
In the next lesson, we'll cover three more inbuilt string functions: Trim, Len, and Space.
Trim, Len, and Space >
Lots more free online course here on our main Home and Learn site
© All course material copyright Ken Carney
5
Excel VBA Programming
Home
Getting Started
8 part section >>
VBA Programming Variables
6 Part Section >>
Conditional Logic
9 part section >>
Strings and String Functions
Text Strings in Excel VBA
Changing Case
Trim, Len, and Space
Excel VBA Replace
InStr, InStrRev, StrReverse
The Left and Right Functions
The Mid Function
A String Exercise
Programming Loops
4 part section >>
Programming Arrays
4 part section >>
Subs and Functions
6 part section >>
Excel VBA and Text Files
2 part section >>
Excel VBA and User Forms
5 part section >>
An Excel Picture Viewer Project
12 part section >>
Excel VBA and Charts
4 part section >>
A TreeView Project
A 4 part section >>
> BUY THE BOOK OF THIS COURSE <
Trim, Len, and Space in Excel VBA
These three string functions are not related, but they are easy enough to use. We'll use them
together in this next example. You can use your Excel workbook and code from the previous
section.
The Trim function is used to trim unwanted white space for text. So if you had the following string:
" some text "
Using Trim on it would remove the spaces to leave this:
"some text"
The Len function is used to get how many characters a string has.
Create another Sub in your code window. Call it TrimAndLen. Add the following code:
Dim FullName As String
Dim LengthFullName As Integer
FullName = " David Gilmour "
LengthFullName = Len(FullName)
MsgBox LengthFullName
6
We've set up two variables here, one called FullName and one called LengthFullName. The
LengthFullName variable has been set up as an Integer. Into the variable called FullName we've
stored the text " David Gilmour ". But notice where the double quotes are. We have three
blank spaces to the left of the name and three blank spaces to the right of the name.
The fourth line is this:
LengthFullName = Len(FullName)
We're using the Len function to the right of an equal sign. In between the round brackets of Len,
we have our FullName variable. The Len function will count how many characters are in the text
that we've stored inside of FullName. When VBA has an answer to the Len function it stores it into
the variable called LengthFullName. Because the Len function counts characters, the value
returned will be an Integer.
Run the code and you'll find that the message box displays the number 19.
However, the name David Gilmour is only 12 characters long. Add the space and it 13 characters.
The message box is displaying 19 because it has counted the extra space at the beginning and the
end.
To remove the space, use the Trim function:
FullName = Trim(" David Gilmour ")
The variable or direct text you're trying to trim goes between round brackets. VBA will then remove
any white space from the front and the end of your string.
Run the code again and the message box displays a value of 13.
Space
You might actually want to pad out a string with blank space. If so, the Space function is the one
you want. In between the round brackets, you type a number. This number is how many space
characters you want. Here's some code to illustrate this:
Dim FullName As String
FullName = "David Glimour"
MsgBox Len(FullName)
FullName = Space(5) & FullName
MsgBox Len(FullName)
The first message box display a value of 13, which is how many characters are in the name David
Gilmour. The second message box displays a value of 18, the 13 original characters, plus 5 added
to the start of the name.
We could have added 5 blank spaces to the end of the name with this:
FullName = FullName & Space(5)
You might be confused about the use of the FullName variable twice, here. But start after the
equal sign and it will make sense. We have this after the equal sign:
FullName & Space(5)
This says, "Take whatever is in the variable called FullName and join 5 space characters to it."
(The & symbol is used to join things together, remember. This is called concatenation.) Once VBA
has joined the text and the space, it needs to store it somewhere. Whatever is to the left of the
equal sign is the place where it will be stored. To the left of the equal sign, we have the FullName
variable again. Whatever was previously in the variable will be replaced. It will be replaced by the
value from the right of the equal sign, which was the name plus 5 characters.
In the next lesson, we'll take a look at the Replace function in Excel VBA.
Replace in Excel VBA >
Lots more free online course here on our main Home and Learn site
© All course material copyright Ken Carney
7
Excel VBA Programming
Home
Getting Started
8 part section >>
VBA Programming Variables
6 Part Section >>
Conditional Logic
9 part section >>
Strings and String Functions
Text Strings in Excel VBA
Changing Case
Trim, Len, and Space
Excel VBA Replace
InStr, InStrRev, StrReverse
The Left and Right Functions
The Mid Function
A String Exercise
Programming Loops
4 part section >>
Programming Arrays
4 part section >>
Subs and Functions
6 part section >>
Excel VBA and Text Files
2 part section >>
Excel VBA and User Forms
5 part section >>
An Excel Picture Viewer Project
12 part section >>
Excel VBA and Charts
4 part section >>
A TreeView Project
A 4 part section >>
> BUY THE BOOK OF THIS COURSE <
Excel VBA Replace Function
The Replace function is used to replace text in a string with something else. Suppose, for
example, that you have a misspelled word in cell A5. You can use Replace to change the incorrect
letters with the correct ones.
You can use your spreadsheet and code from the Change Case section for this. To try it out, add
two more headings in cells A4 and B4. Type the heading Original in cell A4 and the heading
Replace in cell B4. Now click inside cell A5 and type the misspelled word Micrasaft. Your
spreadsheet should now look like this:
To use the Replace function, you need at least three things between it round brackets:
Replace( string_to_search, string_to_replace, replace_with )
8
The first thing you need is a string of text to search. Next, you specify what it is you're searching
for. This is the character or characters you're going to replace. The third thing you need is the new
character or characters.
With the Replace function you also have an optional three things you can specify. These are:
start, count, compare
The optional parameters go after the third item in replace, with each being separated by a comma:
Replace( string_to_search, string_to_replace, replace_with, start, count, compare )
The start parameter is where in the string you want to start search from. The default is character 1,
which is the first character in the string. If you want to start from a position in the string other than
the first character then you need to type your start number here.
The count parameter is how many occurrences you want to replace. The default is to replace
every occurrence of replace_with. If you only want to replace, say, the first two occurrences then
type the number 2 here.
The compare parameter has three options: vbBinaryCompare, vbTextCompare,
vbDatabaseCompare. Don't worry about compare, as it's rarely used.
As an example, add a new Sub to your coding window. Call it ReplaceExample. Add the following
code for the new Sub:
Dim OriginalText As String
Dim CorrectedText As String
OriginalText = Range("A5").Value
CorrectedText = Replace(OriginalText, "a", "o")
Range("A5").Offset(, 1).Value = CorrectedText
Your coding window will then look like this
We have two String variables set up here, OriginalText and CorrectedText. The value for the
OriginalText variable is coming from the Range A5 on the spreadsheet. We then have this:
CorrectedText = Replace(OriginalText, "a", "o")
So we have our Replace function on the right of the equal sign. The first item between the round
brackets of Replace is the variable name OriginalText. This is the text that Replace will be
searching. The next item is the character that is incorrect, the letter "a". The "a" is surrounded by
double quotes. Finally, we need the new text that we want in the string, which is the letter "o". All
three items are separated by commas.
The final line puts the corrected text into cell B5 on the spreadsheet.
Run your code and try it out. Your spreadsheet should change to this:
You can replace more than one character, if you need to. The following code replaces the
misspelled Microsft with Microsoft:
CorrectedText = Replace(OriginalText, "sft", "soft")
9
You can replace spaces in text by typing two double quotes. The first set of double quotes will
have a space between them while the second set has no space. For example:
CorrectedText = Replace("M i c r o s o f t ", " ", "")
This time, the word Microsoft has a space after every letter. We want to remove the space. The
second parameter of the Replace function is two double quotes with a space between them. The
third parameter of the Replace function is two double quotes with no space between them. Two
double quotes together mean "no characters".
In the next lesson, we'll look at three more Excel VBA string functions: InStr, InStrRev,
StrReverse.
InStr, InStrRev, StrReverse >
Lots more free online course here on our main Home and Learn site
© All course material copyright Ken Carney
10
Excel VBA Programming
Home
Getting Started
8 part section >>
VBA Programming Variables
6 Part Section >>
Conditional Logic
9 part section >>
Strings and String Functions
Text Strings in Excel VBA
Changing Case
Trim, Len, and Space
Excel VBA Replace
InStr, InStrRev, StrReverse
The Left and Right Functions
The Mid Function
A String Exercise
Programming Loops
4 part section >>
Programming Arrays
4 part section >>
Subs and Functions
6 part section >>
Excel VBA and Text Files
2 part section >>
Excel VBA and User Forms
5 part section >>
An Excel Picture Viewer Project
12 part section >>
Excel VBA and Charts
4 part section >>
A TreeView Project
A 4 part section >>
> BUY THE BOOK OF THIS COURSE <
Excel VBA Functions: InStr, InStrRev, StrReverse
InStr is short for InString. This string function is used to search for one string inside another. You
need at least two items between the round brackets of the InStr function: the text to search, and
what you want to find. VBA will then give you an Integer back in return. This number will be 0 if the
string is not found. If the string is found then you get the location of the start of the string you were
search for.
Here's an example for you to try (you can use your spreadsheet from the previous section for this):
Dim Email As String
Dim Location As Integer
Email = "myaddress@[Link]"
Location = InStr(Email, "@")
MsgBox Location
We've set up two variables. One is a String variable that holds an email address, and the other is
an Integer called Location. The InStr line is this:
Location = InStr(Email, "@")
The first item between the round brackets is our Email variable. The second item is what we want
to search for in the email address. If the @ sign is not in the Email variable then VBA will place a 0
in the Location variable.
11
When the above code is run the message box will display the number 10. That's because the @
sign is the tenth character in the email address string.
Now delete the @ sign from the Email line:
Email = "[Link]"
Run the code again and the message box displays a value of 0. You can use this for a basic test
on email addresses:
Dim Email As String
Dim Location As Integer
Email = "[Link]"
Location = InStr(Email, "@")
If Location = 0 Then
MsgBox "Not a valid email address"
Else
MsgBox "email address OK"
End If
Two optional parameters for InStr are start and compare:
InStr(start, Text_To_Search, Find, comapre)
If you miss out the start number then InStr searches from the beginning of your string. If you type a
number for start then InStr starts the search from that number in the string.
The compare parameter has four options: vbUseCompareOption, vbBinaryCompare,
vbTextCompare, vbDatabaseCompare. Don't worry about compare, as it's rarely used.
Similar to Instr is InStrRev. The Rev stands for Reverse. This function is the same as InStr but the
difference is that InStrRev starts the search from the end of the string rather than the beginning.
StrReverse
This one is quite easy to use. As its name suggest StrReverse reverses the letters in a string of
text. Here's some code to try:
Dim OriginalText As String
Dim ReversedText As String
OriginalText = "some text"
ReversedText = StrReverse(OriginalText)
MsgBox (ReversedText)
When the code is run, the message box will display "txet emos", which is "some text" reversed.
In the next lesson, we'll take a look at two more Excel VBA functions: Left and Right.
Left and Right >
Lots more free online course here on our main Home and Learn site
© All course material copyright Ken Carney
12
Excel VBA Programming
Home
Getting Started
8 part section >>
VBA Programming Variables
6 Part Section >>
Conditional Logic
9 part section >>
Strings and String Functions
Text Strings in Excel VBA
Changing Case
Trim, Len, and Space
Excel VBA Replace
InStr, InStrRev, StrReverse
The Left and Right Functions
The Mid Function
A String Exercise
Programming Loops
4 part section >>
Programming Arrays
4 part section >>
Subs and Functions
6 part section >>
Excel VBA and Text Files
2 part section >>
Excel VBA and User Forms
5 part section >>
An Excel Picture Viewer Project
12 part section >>
Excel VBA and Charts
4 part section >>
A TreeView Project
A 4 part section >>
> BUY THE BOOK OF THIS COURSE <
Excel VBA Left and Right functions
The Left and Right functions are used to chop characters from a string. Use Left to chop
characters from the start of the string; use Right to chop characters starting from the end of the
string. In between the round brackets of Left and Right you type the number of characters you
want to chop. If you miss out the number of characters to chop then Left and Right extract just one
character from the start or end of the string. Some example might clear things up.
Create a new Sub and try this code out (you can use your spreadsheet from the previous section
for this:
Dim Email As String
Email = "myaddress@[Link]"
MsgBox Left(Email, 9)
MsgBox Right(Email, 9)
The first two lines just set up a String variable and place an email address in the Email variable.
The third line is a message box that uses the Left function:
MsgBox Left(Email, 9)
When you run the code you'll see that the message box displays the first 9 characters of the email
address, everything to the left of the @ sign.
13
The fourth line is this:
MsgBox Right(Email, 9)
The Right function will display 9 characters starting from the final character in the email address,
everything to the right of the @ sign.
That's fairly straightforward, we're sure you'll agree. But now for a more complex use of Left and
Right.
Suppose you have a full name in cell A1 in this format:
David Gilmour
However, suppose you want to have the surname first then the first name. This format:
Gilmour, David
You can use Left, Right and the InStr Functions to achieve this.
Create a new Sub and call it LastFirst. Now set up four variables, three Strings and an Integer:
Dim FullName As String
Dim FirstName As String
Dim LastName As String
Dim SpacePos As Integer
Place the full name in the FullName variable:
FullName = "David Gilmour"
Now use InStr to locate the position of the space in the name:
SpacePos = InStr(FullName, " ")
To get just the first name you can start at the beginning of the full name and go up to the
SpacePos minus 1:
FirstName = Left(FullName, SpacePos 1)
The reason why you need to deduct 1 from the SpacePos variable is because the InStr function
will return the position of the space, a value of 6 for our name. The final character of the first name,
however, is 1 less than this, as David only has 5 characters in it.
To get the last name, we need something slightly different. The starting position is the length of the
full name minus the length of the first name. This will get us the correct number of characters to
grab starting from the right of the name. The code is this:
LastName = Right(FullName, Len(FullName) Len(FirstName))
So as the final parameter of Right we have this:
Len(FullName) Len(FirstName)
This uses the Len function to get the length of the FullName and FirstName variables.
Finally, display the results in a message box:
MsgBox (LastName & ", " & FirstName)
We have the LastName variable first and then the FirstName. The two are separated by
concatenation symbols (&). We also need a comma, and we have this in double quotes so that
VBA sees it as text. So we're saying, "Join together the Last Name, then a comma, then the First
Name".
The whole of your code, then, should look like this:
14
Run your code and you should see this message box:
Click OK to return to your code. Now type a new name. Change this line, for example:
FullName = "David Gilmour"
to this:
FullName = "William Shakespeare"
Run your code again and the message box will display this:
That final exercise illustrates that the more string functions you know and are comfortable with the
more you can achieve in your programming.
(NOTE: The above code only works for names that have two parts. It will fall down if the name is,
say, David Lloyd George. But there is an easier way to do the exercise above, and one that will
cover names of any length: by using the Split function. You'll see how to achieve all this after we
cover something called Arrays. If you want to jump ahead, the page is here: Arrays and the Split
function.)
In the next lesson, we'll look at one final string function Mid.
Mid in Excel VBA >
Lots more free online course here on our main Home and Learn site
© All course material copyright Ken Carney
15
Excel VBA Programming
Home
Getting Started
8 part section >>
VBA Programming Variables
6 Part Section >>
Conditional Logic
9 part section >>
Strings and String Functions
Text Strings in Excel VBA
Changing Case
Trim, Len, and Space
Excel VBA Replace
InStr, InStrRev, StrReverse
The Left and Right Functions
The Mid Function
A String Exercise
Programming Loops
4 part section >>
Programming Arrays
4 part section >>
Subs and Functions
6 part section >>
Excel VBA and Text Files
2 part section >>
Excel VBA and User Forms
5 part section >>
An Excel Picture Viewer Project
12 part section >>
Excel VBA and Charts
4 part section >>
A TreeView Project
A 4 part section >>
> BUY THE BOOK OF THIS COURSE <
The Mid Function in Excel VBA
The final String function we'll look at is Mid. This function is used to grab characters from a string
of text. It has three parts:
Mid(string_to_search, start_position, number_of_characters_to_grab)
The first part is the string you want search. This can be a variable or direct text between double
quotes. The second part is where in the string you want to start grabbing characters from. The final
part is how many characters you want to grab.
To demonstrate the Mid function, examine the following code:
Dim Email As String
Dim GrabbedChars As String
Email = "myaddress@[Link]"
GrabbedChars = Mid(Email, 16, 4)
MsgBox GrabbedChars
We've set up two String variables here, one called Email and one called GrabbedChars. We've
stored an email address in the Email variable. Then comes our Mid code:
GrabbedChars = Mid(Email, 16, 4)
16
The text we're searching is in the Email variable. We want to start grabbing characters from
position 16 in the string. The numbers of characters we want to grab is 4.
When the programme is run, the message box will display .com.
The Mid function is very useful in loops, as it allows you to examine one character at a time from a
string of text.
In the next lesson, we'll present you with a probem that will test your new String Method skills.
An Excel VBA String Exercise >
Lots more free online course here on our main Home and Learn site
© All course material copyright Ken Carney
17
Excel VBA Programming
Home
Getting Started
8 part section >>
VBA Programming Variables
6 Part Section >>
Conditional Logic
9 part section >>
Strings and String Functions
Text Strings in Excel VBA
Changing Case
Trim, Len, and Space
Excel VBA Replace
InStr, InStrRev, StrReverse
The Left and Right Functions
The Mid Function
A String Exercise
Programming Loops
4 part section >>
Programming Arrays
4 part section >>
Subs and Functions
6 part section >>
Excel VBA and Text Files
2 part section >>
Excel VBA and User Forms
5 part section >>
An Excel Picture Viewer Project
12 part section >>
Excel VBA and Charts
4 part section >>
A TreeView Project
A 6 part section >>
An Excel VBA String Method Exercise
To get some practice with String methods, we're going to work through a problem. Try to solve the
problem yourself before going through the solution below. OK, here's the problem.
Problem
Suppose you had a product code on a spreadsheet that looked like this:
PD232345
However, it's in the wrong format. First, you're told to remove all the hyphens. Then, you're told to
turn the letters PD in the product code to PDC. So your finished work should look like this:
PDC232345
The question is, how would you do this with Excel VBA code?
If you get stuck solving the problem above, then here's the solution.
Solution
18
The first part of the problem, removing the hyphens, is fairly easy just use Replace:
Dim ProductCode As String
ProductCode = "PD232345"
ProductCode = Replace(ProductCode, "", "")
In between the round brackets of Replace we have the text we want to search, which is the
variable called ProductCode. After a comma, we have the character we want to replace, the
hyphen. Next, we have the new character, which is no text at all. This is done with two double
quotes with no space between them.
The second part of the problem, adding the "C" after "PD", would be easy too, if VBA had an Insert
function. But it doesn't. Which makes this part of the problem a little bit harder.
There are a few ways to insert the "C" in the correct place. We'll do it using the Left and Mid string
functions. We'll use Left to grab the first two characters, then add the "C". We'll then use Mid to get
the numbers.
To get the first two characters and add the "C", the code is this:
Dim Letters As String
Letters = Left(ProductCode, 2) & "C"
Starting from the left of ProductCode, we grab two characters:
Left(ProductCode, 2)
The "C" is added with concatenation:
Left(ProductCode, 2) & "C"
The new three letter code is then stored in the variable called Letters.
To get the numbers, use Mid:
Dim Numbers As String
Numbers = Mid(ProductCode, 3)
Mid first needs the string you're searching, which is ProductCode for us. Next, we have 3 as the
starting point to grab characters. This will grab the characters starting from the 3rd character in the
string. Because we haven't specified an end number, Mid will grab the rest of the characters to the
end of the string.
The only thing left to do is to join the two parts together:
Dim NewCode As String
NewCode = Letters & Numbers
MsgBox NewCode
This just uses concatenation to join the Letters variable to the Numbers variable. The final line
uses a message box to display the results. The whole of the code, though, looks like this:
When you meet a problem like the one above, the solution is usually to use one of the Left, Right,
or Mid functions (or all of them) to chop the string into pieces and then join them back together
again. In the next lesson, we'll move on and tackle programming loops.
19
Loops in Excel VBA >
Lots more free online course here on our main Home and Learn site
© All course material copyright Ken Carney
20