Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete everything after a certain character
HI hope some one can help
i have been tring to wolr this out myself by reading previous post but i still none the wiser i have a column of numbers and what i need to do is is ther is a / in the field i need to delete it and any numbers after it can someone please help i have looked at other examples but dont fully understand what they do Andy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete everything after a certain character
Sub delslash() For Each c In Selection If InStr(c, "/") 0 Then c.Value = Left(c, Len(c) - InStr(c, "/")) End If Next End Sub -- Don Guillett SalesAid Software wrote in message ups.com... HI hope some one can help i have been tring to wolr this out myself by reading previous post but i still none the wiser i have a column of numbers and what i need to do is is ther is a / in the field i need to delete it and any numbers after it can someone please help i have looked at other examples but dont fully understand what they do Andy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete everything after a certain character
Try this user defined function. If the number is in Cell A1 then in B1 you
will enter =GetNumerator(A1). Copy and paste to other cells as needed. As far as the function is concerned, you will need to place it in one of the modules. The function works by finding out the formula in the cell and then taking the numerator if the contents of the cell if the cell happen to contain a formula. The approach is somewhat different if the content of the cell is text. Public Function GetNumerator(ByVal v As Range) As Variant Application.Volatile Set v = v.Cells(1) If InStr(v.Formula, "/") 0 Then If Left(Left(v.Formula, InStr(v.Formula, "/") - 1), 1) = "=" Then GetNumerator = Evaluate(Left(v.Formula, InStr(v.Formula, "/") - 1)) Else GetNumerator = Left(v.Formula, InStr(v.Formula, "/") - 1) End If Else GetNumerator = v.Value End If End Function " wrote: HI hope some one can help i have been tring to wolr this out myself by reading previous post but i still none the wiser i have a column of numbers and what i need to do is is ther is a / in the field i need to delete it and any numbers after it can someone please help i have looked at other examples but dont fully understand what they do Andy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete everything after a certain character
You could just take the Val of the string and it will return only the
numerical part before the / Val("1234/XYZ") returns 1234 Sub SlashDelete() Dim c As Range For Each c In Selection c = Val(c) Next End Sub -- Cheers Nigel wrote in message ups.com... HI hope some one can help i have been tring to wolr this out myself by reading previous post but i still none the wiser i have a column of numbers and what i need to do is is ther is a / in the field i need to delete it and any numbers after it can someone please help i have looked at other examples but dont fully understand what they do Andy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete everything after a certain character
Another way is to select the range
edit|replace what: /* (slash asterisk) with: (leave blank) replace all Be aware that if you have 00012/1313abc excel will be left with 00012 and that's just the plain old number 12. And that's what you'll see unless/until you apply formatting to the cell Like: format|cells|number tab|custom category|00000 wrote: HI hope some one can help i have been tring to wolr this out myself by reading previous post but i still none the wiser i have a column of numbers and what i need to do is is ther is a / in the field i need to delete it and any numbers after it can someone please help i have looked at other examples but dont fully understand what they do Andy -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete special character | Excel Discussion (Misc queries) | |||
Delete all occurrences of a character | New Users to Excel | |||
Delete first character in ActiveCell | Excel Programming | |||
Delete everything after a certain character? | Excel Programming | |||
Delete Asc(10) character from cell | Excel Programming |