Extract Characters to the Right of the Right-Most Space in the String, in VBA

[DougRobertson]

Hello,

I'm looking for VBA Code that will Extract all the Characters to the Right of the Right-Most Space in a String.

Where Cell "C6" contains the Text "From 8/1/2018 To 8/7/2018", I need to have the Variable iEndDate return the characters "8/7/2018".

Thank you in advance for any and all assistance!

(Using Excel 2016)

~ DWR

 

[pike]

Hello,
One way would be to use " To " as a delimiter in the split function.

Option Explicit
Option Explicit
Sub splitstr()
 Dim str As String
    str = Split(Range("C6"), " To ")(1)
 MsgBox str
End Sub

 

[DougRobertson]

Bingo! Works to perfection!

Could you kindly explain what a Delimiter is and how it is used, and also what the "(1)" at the end of the formula does?

With much thanks!

~ DWR

 

[pike]

Hello,
The best info on each of the functions and split function are explained at the Microsoft Development centre.

https://msdn.microsoft.com/en-us/VB...tFrameworkMoniker-Office.Version=v16)&rd=true

Delimiter is the String on which to separate the string into parts. usually a space.
The (1) is the second occurrence/part of the string ( your date), try (0) and see the result (it will be the first date)

 

[Peter_SSs]

You can also use something like this

Sub RightPart()
  Dim iEndDate As String
  
  iEndDate = Mid(Range("C6").Value, InStrRev(Range("C6").Value, " ") + 1)
End Sub

On a side-note, I would definitely avoid using str as a variable in vba as that is one of vba's built-in function names.

 

[pike]

Hi Peter,
yes true. Ill edit

 

[DougRobertson]

I just KNEW I could count on the Commonwealth to come through for me!

The Split function works best for my situation now, but I've also been looking for some help with the Mid function as well - thanks Guys!!

~ DWR

 

[pike]

yes, always carrying you fellas and god save the Queen.