how do i delete everything after a particular character, after each such character?

[gilgilgil]

i have about 1,800 rows of text data, some of which need to be cleane
as follows:

about 2/3 of the 1,800 rows have text that appears after a dash whic
is preceded and followed by a space (" - ") and this post-dash tex
needs to be deleted.

ideally, there would be some way to tell excel to check each cell t
see whether there is a " - " in the text data and, if there is, t
delete the dash and everything AFTER the dash (but nothing before it)
this way, all cells containing no dash at all remain unaffected, an
the cells containing a dash lose everything that appears after th
dash.

if anyone knows how to do this, i hereby dub thee an excel genius wh
just made one person's life much more pleasant.

if not, perhaps you can suggest some great road-trip music to keep m
awake as i manually clean up about 1,200 lines of text data... :-

 

[Paul Corrado]

Try

Edit/Replace

Find What: enter " - *" (without the ""s)
Replace With: enter " -" (if you still want the dash otherwise leave that
field blank)

Select Replace all

Obviously, save the file first just in case

 

[Ron Rosenfeld]

i have about 1,800 rows of text data, some of which need to be cleaned
as follows:

about 2/3 of the 1,800 rows have text that appears after a dash which
is preceded and followed by a space (" - ") and this post-dash text
needs to be deleted.

ideally, there would be some way to tell excel to check each cell to
see whether there is a " - " in the text data and, if there is, to
delete the dash and everything AFTER the dash (but nothing before it).
this way, all cells containing no dash at all remain unaffected, and
the cells containing a dash lose everything that appears after the
dash.

if anyone knows how to do this, i hereby dub thee an excel genius who
just made one person's life much more pleasant.

if not, perhaps you can suggest some great road-trip music to keep me
awake as i manually clean up about 1,200 lines of text data...

For a formula solution:

=REPLACE(A1,FIND(" - ",A1),255,"")


--ron

 

[mudraker]

Paul

This macro will delete everthing after any entry in coulmn A that has
" - " in it
it also deletes the " - "

Sub CleanText()
Dim iPos As Integer
Dim c As Range

For Each c In Range("a1:a" & Range("a" & Rows.Count).End(xlUp).Row)
iPos = InStr(1, c.Value, " - ")
If iPos > 0 Then
c.Value = Left(c.Value, iPos - 1)
End If
Next
End Su