How to split text based on multiple delimiter like dash (-) ?

[dmadhup]

hello,

I am using the following code to split

For i = 2 To lastRow
fullname = Cells(i, 2).Value
myString = Trim(Cells(i, 2).Value)
If InStr(myString, "SPLICE") > 0 Or InStr(myString, "splice") > 0 Then
Cells(i, 16).Value = Cells(i, 2).Value ''16 is column P
ElseIf InStr(myString, "-") > 0 Then
dashPosition = InStr(fullname, "-")
Cells(i, 14).Value = Mid(fullname, dashPosition + 1) ''14 is column N
Cells(i, 15).Value = Left(fullname, dashPosition - 1) ''15 is column o
Cells(i, 16).Value = Cells(i, 13) & " " & Cells(i, 15) ''16 is column P
End If
Next i

It works for me if the string contains one dash. For example, J2-33 results: J2 and 33.

However, If the string is BMS-J3-24 then it doesn't work. I am expecting the result BMS-J3 in column N and 24 in column O

Any help is appreciated.

 

[Rick Rothstein]

I am not clear on where you want your output, but if your ultimate desire is to place everything before the last dash in one cell and what is after that last dash in another cell, you should use the InStrRev function to locate the position of the last dash in a text string, then use that number in the Left and Mid functions to pull out the required text strings. So, as an example, if you have this...

FullText = "BMS-J3-24"

then...

LastDash = InStrRev(FullText, "-")
FirstPart = Left(FullText, LastDash - 1)
SecondPart = Mid(FullText, LastDash + 1)

 

[dmadhup]

Thank you, Rick, for your response to my question.
It works for me. Really appreciate your help.