Need help stripping characters from beginning of string

[kcleere]

Greetings,

I need some VBA code that will strip all characters including and prior to the first space...after a dash in a string. This data will always be in column B.

Example:

18 - John Doe

Would return John Doe.

I have tried but I have not yet nailed it so in seeking help from the brain trust here.

Thank you in advance.

Ken

 

[Jonmo1]

Try the SPLIT function..

Something like

MyStr = "18 - John Doe"
MyVals = Split(MyStr, "- ")
MsgBox MyVals(1)

 

[kcleere]

I'm not familiar enough with VBA to understand how this will only act on col B. Please advise?

 

[Jonmo1]

Try

Sub Strip_Beginning()
Dim lr As Long, i As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
For i = 2 To lr
    If Instr(1,Cells(i, "B").Value, " -") <> 0 Then
        Cells(i, "B").Value = Split(Cells(i, "B").Value, "- ")(1)
    End If
Next i
End Sub

 

[kcleere]

Thanks I'll give that a try

 

[kcleere]

It worked perfectly...thank you. I may be back for more..
Thanks again!

 

[Jonmo1]

You're welcome.

 

[Rick Rothstein]

It worked perfectly...thank you. I may be back for more..

Just so you know, you can do what you asked for manually just as easily. Assuming cell B1 does not contain a dash followed by a space, select Column B (otherwise, select from cell B2 on down), press CTRL+H to bring up Excel's Replace dialog box, put "*- " without the quote marks (that is an asterisk followed by a dash followed by a space character) in the "Find what" field, leave the "Replace with" field empty, click the "Options>>" button and make sure the "Match entire cell contents" checkbox is not checked, then click the "Replace All" button.

If, however, you want to still do it with a macro, this compact one will work as well (it just codifies what I described above)...

Sub StripFromBeginningToDashSpace()
  Range("B2", Cells(Rows.Count, "B").End(xlUp)).Replace "*- ", "", xlPart
End Sub

 

[kcleere]

Thank you Rick....the manual method will work yes but I'm giving this to a user and I need them to click as little as possible.