Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
REPLACE or ??, Help please
I think you would want the screen updating to be set to True at the end
of the function! Pete |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Edit Replace | Excel Discussion (Misc queries) | |||
find replace format | Excel Discussion (Misc queries) | |||
Search and replace | Excel Worksheet Functions | |||
replace absolute references | Excel Worksheet Functions | |||
REPLACE outside of highlighted column | Excel Worksheet Functions |