CODEWITHSUNDEEP
excelvba
Hakon
Hakon

Reputation: 99

Incorrectly using the Right function

When I use my code, and I run through with Array = "0-28", Arrex comes out equaling "28" which is what I want. However when I run through using Array = "0-2", Arrex comes out with "-2". This is not what I want as I just want what is on the right of the hyphen. I thought that the right function would take to the right of the string while I was looking for the hyphen but I must have messed up somewhere.Any help would be greatly appreciated

I looked at Arrex = Right(TestText, DashSearch +1) and Arrex = Right(TestText, DashSearch -1) but these both expectedly failed.

Dim Array As String
Dim Arrex As String
Dim DashSearch As Integer

TestTextOne = "0-2" 'First Test
TestTextTwo = "0-28" 'Second Test
DashSearch = InStr(TextTestOne, "-")
If (DashSearch > 0) Then
Arrex = Right(TextTestOne, DashSearch)
Else
Arrex = TextTestOne
End If

Upvotes: 0

Views: 41

Answers (2)

Storax
Storax

Reputation: 12207

To fix your code you need to adjust Right, nothing else

Option Explicit

Sub TestIt()
    Dim vArray As String
    Dim Arrex As String
    Dim DashSearch As Integer

    vArray = "0-2" 'First Test
    vArray = "0-28" 'Second Test
    DashSearch = InStr(1, vArray, "-", vbTextCompare)
    If (DashSearch > 0) Then
        Arrex = Right(vArray, Len(vArray) - DashSearch)
    Else
        Arrex = vArray
    End If
End Sub

Upvotes: 1

SJR
SJR

Reputation: 23081

I would use Split, which will split using a delimiter, e.g.

Sub x()

Dim Array1 As String
Dim Arrex As String
Dim DashSearch As Variant

Array1 = "0-2" 'First Test
'Array1 = "0-28" 'Second Test
DashSearch = Split(Array1, "-")

If UBound(DashSearch) > 0 Then
    Arrex = DashSearch(1)
Else
    Arrex = Array1
End If

End Sub

You could do this with formulae - LEFT/MID/RIGHT and FIND.

=IFERROR(MID(A1,FIND("-",A1)+1,255),A1)

Upvotes: 3

Related Questions