VBA evaluate cell and remove everything after the dash

[FryGirl]

I'm hoping to use the evaluate function in VBA to keep everything before the dash in row 1. If there isn't a dash in the data then just keep what's already in the cell.

Example data:

POV - Private Vehicle	CAT	MPH - Miles Per Hour
POV	CAT	MPH

The first row is row 1, and just using row 2 to show you what I want replaced directly in the cells of row 1.

This is close, but the .value line is not right.

Sub RemoveData()
    Dim LastCol As Long: LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    With Range(Cells(1, 1), Cells(1, LastCol))
        .Value = Evaluate("IF(ISNUMBER(SEARCH(""-"",@)),LEFT(@,FIND(""-"",@)-2),@))
    End With
End Sub

 

[Fluff]

How about

        .Value = Evaluate(Replace("IF(ISNUMBER(SEARCH(""-"",@)),LEFT(@,FIND(""-"",@)-2),@)", "@", .Address))

 

[Rick Rothstein]

Looks like this one-liner should also work...

Sub RemoveData()
  Range("A1", Cells(1, Columns.Count).End(xlToLeft)).Replace " -*", "", xlPart, , False, , False, False
End Sub

 

[FryGirl]

Thanks Fluff. That works perfect.

Thanks Rick. This worked great as well.

 

[Rick Rothstein]

Thanks Rick. This worked great as well.

You are quite welcome. One thought on this... since you are processing all cells on Row 1, my code can be simplified...

Sub RemoveData()
  [1:1].Replace " -*", "", xlPart, , False, , False, False
End Sub

And just so you know, my code is nothing more than the automating of selecting Row 1, pressing CTRL+H to bring up the Replace dialog box, putting " -*" (without the quote marks) in the Find What field, leaving the Replace With field empty, clicking the "Options>>" button to make sure both checkboxes are not checked, then clicking OK.

 

[FryGirl]

Thanks for the update Rick.