Menu
I have multiple strings in different cells like
CO20: 20 YR CONVENTIONAL
FH30: 30 YR FHLMC
FHA31
I need to get the substring from 1 to till index of ':' or if that is not available till ending(in case of string 3). I need help writing this in VBA.
471
Step 1: In the same module declare a sub-function to start writing the code for sub-function. Step 2: Declare two Variables A as string and B as String array and take input string from the user and store it in Variable A. Step 3: Use the Split SubString function and store its value in Variable B.
Excel VBA SubString. A SubString is a part of the string or portion, or the character of the string is called “SubString.” There are three types of SubString functions in VBA: LEFT, RIGHT, and MID. They are similar to the worksheet substrings in Excel. A string is nothing but a series of characters.
You can do this using the InStr and Mid functions. Use the InStr function to find the occurrences of the / and then use Mid to get the part of the string that you are interested in. This function will produce the desired results. Save this answer.
The InStr built-in function can be used to test if a String contains a substring. The InStr will either return the index of the first match, or 0. Is Optional. Numeric expression that sets the starting position for each search.
Shorter:
Split(stringval,":")(0)
Test for ':' first, then take test string up to ':' or end, depending on if it was found
Dim strResult As String
' Position of :
intPos = InStr(1, strTest, ":")
If intPos > 0 Then
' : found, so take up to :
strResult = Left(strTest, intPos - 1)
Else
' : not found, so take whole string
strResult = strTest
End If
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
