REPLACE or ??, Help please
 

First, Thank you for all the help and support.

I have a many cells similar to 01037DL530001-101 and want to remove
everything from the "-" to the end. I tried replace but can't seem to get it
to work. The one thing to keep in mind is that the numbers of characters or
numbers varies after the dash.

Thank you for your help
Joe

REPLACE or ??, Help please
 

You could use code. The below is set to use the current selection as
its target range, but you could use a static range or the entire used
range.

Sub TruncCells()
Dim c As Range, i As Integer
Application.ScreenUpdating = False
For Each c In Selection
If Not c.HasFormula Then
i = InStr(1, c.Value, "-")
If i 0 Then c.Value = Left(c.Value, i - 1)
End If
Next c
Application.ScreenUpdating = False
End Sub

REPLACE or ??, Help please
 

I think you would want the screen updating to be set to True at the end
of the function!

Pete

REPLACE or ??, Help please
 

Yup, that's what I get for copy/pasting ...

Sub TruncCells()
Dim c As Range, i As Integer
Application.ScreenUpdating = False
For Each c In Selection
If Not c.HasFormula Then
i = InStr(1, c.Value, "-")
If i 0 Then c.Value = Left(c.Value, i - 1)
End If
Next c
Application.ScreenUpdating = TRUE ' *********** NOTE EDIT
End Sub

REPLACE or ??, Help please
 

If the number of characters before the dash are consistent, then you may use:

=left(a2,14)

14 is the length of the string before the dash.

thanks.

"Joe Gieder" wrote:

First, Thank you for all the help and support.

I have a many cells similar to 01037DL530001-101 and want to remove
everything from the "-" to the end. I tried replace but can't seem to get it
to work. The one thing to keep in mind is that the numbers of characters or
numbers varies after the dash.

Thank you for your help
Joe