Thread: Deleting everything after certain character

find -* replace with nothing

Hello Tom,

welcome to the forum. You can use the LEFT and FIND functions in a combination in another cell. For your example above:

=LEFT(A1,FIND(CHAR(150),A1))

The dash in your example is the character 0150, for a simple dash as found on the keyboard, use

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

Copy the formula down the column of your data. You can copy the result and use Paste Special - Values to copy only the values elsewhere.

You can have a formula in another cell that does that truncation

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

If you want to do it in place, then you need some VBA.

Code:

' Find all occurrences of char in worksheet and truncate any characters following it
Public Sub TruncAfter(char As String)
   Dim c As Range
   Dim pos As Long
   For Each c In UsedRange
      pos = InStr(c.Value, char)
      If pos > 0 Then
         c.Value = Mid(c.Value, 1, pos)
      End If
   Next c
End Sub

This code is written to go in the sheet with the data. If you want a more general solution use this, which can go in a Module:

Code:

' Find all occurrences of char in worksheet and truncate any characters following it
Public Sub TruncAfter(ws as Worksheet, char As String)
   Dim c As Range
   Dim pos As Long
   For Each c In ws.UsedRange
      pos = InStr(c.Value, char)
      If pos > 0 Then
         c.Value = Mid(c.Value, 1, pos)
      End If
   Next c
End Sub

Originally Posted by martindwilson

find -* replace with nothing

Ooh, that's much better than writing all that code I have to admit I didn't know Excel find & replace supported wildcards.

However, to delete everything after the "-" you would replace -* with -.

edit ah i was just going to say that lol but if it is char 150 then find
hold down alt and key 0150 on the numeric keypad then put * will do it