VBA Function to bring back Left of a string, based on position of a specific character in the string

user_15932330 0
Viewed 98

I'm trying to create a user defined function that returns everything to the left of the last hyphen in a string. All strings will be formatted like this:

  • CT2986-400427-15
  • J84705-202001-12
  • TF230068-003
  • DS2112-000038-11

This is my current code, but it keeps displaying a #VALUE! error in the cell. Any ideas?

Function PartNOSUFFIX(PartNo As String) As String

PartNOSUFFIX = Left(PartNo, InStr(8, PartNo, "-") - 1)

End Function

Thanks in advance!

vba excel
3 Answers

You want to use Instrrev instead which is Instr but in reverse. This way it will always find the last hyphen and return it's position.

So try:

Function PartNOSUFFIX(PartNo As String) As String

PartNOSUFFIX = Left(PartNo, InStrRev(PartNo, "-") - 1)

End Function

You will get a #VALUE! error if the cell is blank or doesn't have a hyphen in it. If you want to account for that you can use something like this instead:

Function PartNOSUFFIX(PartNo As String) As String

If PartNo = "" Or InStr(PartNo, "-") = 0 Then
    PartNOSUFFIX = ""
Else
    PartNOSUFFIX = Left(PartNo, InStrRev(PartNo, "-") - 1)
End If

End Function

I just made it return blank if the cell it's looking at is blank or doesn't have a hyphen. You can change that to whatever you want it to say of course.

user_26321650

A bit complicated but fun try

Function PartNOSUFFIX(PartNo As String) As String
      PartNOSUFFIX = Replace(PartNo, "-" & Split(PartNo, "-")(UBound(Split(PartNo, "-"))), "")
End Function
user_55690080

You can use StrReverse() and Split() to accomplish this quite easily:

Function PartNOSUFFIX(PartNo As String) As String

    PartNOSUFFIX = StrReverse(Split(StrReverse(PartNo), "-", 1)(0))

End Function

Alternatively, you can use a regular expression:

Function PartNOSUFFIX(PartNo As String) As String

With CreateObject("vbscript.regexp")
    .Pattern = "(.*)-.*"
    PartNOSUFFIX = .Replace(PartNo, "$1")
End With

End Function
  • (.*) - Capture 0+ (greedy) characters other than newline in a capture group upto;
  • - - A literal hyphen.
  • .* - Again 0+ (greedy) characters other than newline.

The fact that regular expressions start matching left to right assures that our 1st greedy pattern will consume all characters upto the last hyphen since no other characters come after that leading to the next hyphen.

Note that both options would work even no hyphen is present.

user_97581940
Related