[Solved] Can't split a string the way i want

[shahin]

Hi there all! Hope you are doing well. I got entangled in an issue and couldn't solve it the way i want. I used a string in my code and wanted to get the last part of it using SPLIT and UBOUND function. I made it the way most people tend to avoid. If anyone out there helps me accomplish it in an accurate and succinct way, I would be very happy. Thanks in advance.

EDIT: I used split and ubound function two times here to get the result but my intention is to get the same result using split and ubound function once.

String I have used in Range("A3")= "Harry potter and-the order of phoenix"
First Seperator="-"
Second Seperator=" "
End result= "phoenix"

Here is the code I used:

Sub Splitting()

Dim str As Variant, w As Variant
Dim v As String, m As String, s As String

s = Range("A3").Value

str = Split(s, "-")

v = str(UBound(str))

w = Split(v, " ")

m = w(UBound(w))

Debug.Print m

End Sub

 

[Marc L]

Hi !

To extract last word you only need one Split (on space as default) …
​

 

[shahin]

@Marc L,
I'm so grateful to you. Thanks a ton.

 

[shahin]

By the way, does it always have to be the space as default? Thanks again.

 

[Marc L]

As written in Split VBA inner help, space is the default delimiter.

So when delimiter is not a space, you must specify it in the function …​

 

[Deepak]

This too...

s = Range("A3").Value
Debug.Print Mid(s, InStrRev(s, " ") + 1)