Substring in VBA

Question

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.

Solution 1

Shorter:

   Split(stringval,":")(0)

Solution 2

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

Solution 3

You can first find the position of the string in this case ":"

'position = InStr(StringToSearch, StringToFind)
position = InStr(StringToSearch, ":")

Then use Left(StringToCut, NumberOfCharacterToCut)

Result = Left(StringToSearch, position -1)

Comments

[D Wojciechowski]

-1 if : is not found, Right(StringToSearch, position) will return an empty string. Also, what is NumberOfCharacterToCut? Where did that come from and what is it equal to?

[Pepe]

@Steve NumberOfCharacterToCut: That's what the variables mean and not the function call itself. And 2) I was showing him how to get a substring. I suggest you read and understand before voting...

[D Wojciechowski]

Well, your edit is better, but if there is still a problem. If there is no ':' in StringToSearch, position will =0. When you then call Left(StringToSearch, position-1), you will get a run time error. Plus, calling the second argument to Left() 'NumberOfCharacterToCut' is misleading. The second argument is the length of the string to return, not what is being cut. I don't doubt that you don't understand all this, but I don't think you are expressing yourself very well.

[ray]

Nice! S.., you might want to add an Rtrim() to that to take care trailing spaces (e.g. "FH30 :"), but this is great.