CODEWITHSUNDEEP
excelvba
Boohoolean
Boohoolean

Reputation: 25

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

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:

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!

Upvotes: 1

Views: 2027

Answers (3)

JvdV
JvdV

Reputation: 75950

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.

Upvotes: 1

xShen
xShen

Reputation: 572

A bit complicated but fun try

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

Upvotes: 1

Simon
Simon

Reputation: 1375

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.

Upvotes: 2

Related Questions