How best to remove data from cells

[Sundance_Kid]

Hi there, I have a list of names and numbers all in one with a hyphen separating the name and number. I want to remove the number & hyphen.

Bill Clinton - 1234
Joe Biden - 5678

So, basically all the above appears in a cell & I want to just see it appear like :

Bill Clinton
Joe Biden

Can someone advise on how best to formulate it to remove the hyphen & number from the excel cell.

Thanks

 

[mumps]

Assuming your data is in column A starting in row 2, try:

Sub RemoveData()
    Application.ScreenUpdating = False
    Dim rng As Range
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        rng = Split(rng, " -")(0)
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

A formula option

Fluff.xlsm
	A	B
1
2	Bill Clinton - 1234	Bill Clinton
3	Joe Biden - 5678	Joe Biden
4	Donald Trump	Donald Trump
Main

Cell Formulas
Range	Formula
B2:B3	B2	=TRIM(TEXTBEFORE(A2,"-"))
B4	B4	=TRIM(TEXTBEFORE(A4,"-",,,1))

 

[Fluff]

If you want a macro, another option is

Sub sundance()
   With Range("A2", Range("A" & Rows.count).End(xlUp))
      .Value = Evaluate(Replace("trim(left(@,find(""-"",@&""-"")-1))", "@", .Address))
   End With
End Sub

 

[Sundance_Kid]

Thank you all, I want to avoid trying to use/set-up a macro, so the formula option is probably the best. However, the version of Office the Textbefore option but I am due an upgrade of my office version in the next couple of days, so I will try then.
Thanks

 

[Fluff]

If you don't have the textbefore function yet, you can use

=TRIM(LEFT(A2,FIND("-",A2&"-")-1))