Assigning cell contents after specific text to a variable

[akg742]

I have 2 issues. First, I need to identify the text after " - " in worksheet1 A1 (this cell is always Title - "local site" with "local site" changing based on where the user is). The formula I thought was working (Right(rng, InStr(rng, " ") - 6).Value) grabbed the last 3 digits (my site is ABC) on the right but the length of the site will vary.

Once I have that, I need to use it as part of the subject for an email I want to write using a macro: site " summary " date (for example, ABC Summary April 26)

Dim subj As String
Dim site As String
Dim rng As Range

Sheets("worksheet1").Select
Set rng = Range("A1")

site = Right(rng, InStr(rng, " ") - 6).Value ' this formula won't work for all sites. For now, it returns "ABC"

DateDM = Format(Date, "MMMM DD")

subj = site & " Summary - " & DateDM

When I run the code to start my email, my Subject starts with Summary and nothing I've tried adds on the site.

 

[DanteAmor]

Try his:

The code will get what is after the "-" regardless of the length.

Sub test()
  Dim site As String
  site = Split(Sheets("worksheet1").Range("A1").Value, "-")(1)
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[akg742]

Try his:

The code will get what is after the "-" regardless of the length.

Sub test()
  Dim site As String
  site = Split(Sheets("worksheet1").Range("A1").Value, "-")(1)
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

That worked! Thank you so much