find chr(45) remove it and text to the right

[RAYLWARD102]

if I have some text that looks like: mike turner - manager
I want to correct the text to look like: mike turner
So I Want to detect the - and kill the - and text to the right of it. Any idea's how that would be accomplished?

 

[mvptomlinson]

Try

=LEFT(A1,FIND("-",A1)-1)

Assuming there's always a space before the hyphen. If not, remove the -1 at the end.

 

[Haseeb Avarakkan]

Select the range press Ctrl+F,

Find What: -*

Replace with a blank.

 

[RAYLWARD102]

Here is what I tried. It seems to error at the find statement. Any idea's?

Dim wks1 As Worksheet
    Set wks1 = Worksheets("names")

Dim cell As Range
Dim clense As String
 
 For Each cell In wks1.Range("a40:a42").Cells
    clense = Left(cell.Value, Find("-", cell.Value) - 1)
    wks1.Range("f" & cell.Row).Value = clense
Next cell

 

[mvptomlinson]

Yes, "Find" isn't available in VBA unless you use WorksheetFunction.Find.

Do you have to use VBA? Either option provided above will work.

If you must, change it to:

clense = Left(cell.Value, InStr(1, cell.Value, "-") - 1)

 

[snoopyhr]

Given the text is in A2 and down , in B2 place this formula and copy down

=MID(A2,1,SEARCH("-",A2)-1)

Just another alternative, this way the integrity of original data is perserved (helpful at the time of audit )