Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all Characters "except"
Hi All.........
I don't remember where I got this code, or if it ever worked,....but I need it now to delete all the characters in a cell except the ones indicated not........... I'm pasting it into a regular module and putting =KillText(A1) in cell B1 and all I get is #VALUE! Can anybody see what I'm doing wrong please?........or have a better suggestion? Function KillText(cellinput) As String Set cellinput = Intersect(cellinput.Parent.UsedRange, cellinput) For i = 1 To Len(cellinput) Select Case Mid(cellinput, i, 1) Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, " ", "-", "/","%" charval = Mid(cellinput, i, 1) Case Else charval = "" End Select KillText = KillText & charval Next i End Function TIA Vaya con Dios, Chuck, CABGx3 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all Characters "except"
Function KillText(cellinput) As String
Set cellinput = cellinput(1) CharVal = "" For i = 1 To Len(cellinput) Select Case Mid(cellinput, i, 1) Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, " ", "-", "/", "%" CharVal = CharVal & Mid(cellinput, i, 1) End Select Next i KillText = CharVal End Function worked for me. -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi All......... I don't remember where I got this code, or if it ever worked,....but I need it now to delete all the characters in a cell except the ones indicated not........... I'm pasting it into a regular module and putting =KillText(A1) in cell B1 and all I get is #VALUE! Can anybody see what I'm doing wrong please?........or have a better suggestion? Function KillText(cellinput) As String Set cellinput = Intersect(cellinput.Parent.UsedRange, cellinput) For i = 1 To Len(cellinput) Select Case Mid(cellinput, i, 1) Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, " ", "-", "/","%" charval = Mid(cellinput, i, 1) Case Else charval = "" End Select KillText = KillText & charval Next i End Function TIA Vaya con Dios, Chuck, CABGx3 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete all Characters "except"
Thank you kind Sir..........your version works beautifully for me
too......... Vaya con Dios, Chuck, CABGx3 "Tom Ogilvy" wrote in message ... Function KillText(cellinput) As String Set cellinput = cellinput(1) CharVal = "" For i = 1 To Len(cellinput) Select Case Mid(cellinput, i, 1) Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, " ", "-", "/", "%" CharVal = CharVal & Mid(cellinput, i, 1) End Select Next i KillText = CharVal End Function worked for me. -- Regards, Tom Ogilvy "CLR" wrote in message ... Hi All......... I don't remember where I got this code, or if it ever worked,....but I need it now to delete all the characters in a cell except the ones indicated not........... I'm pasting it into a regular module and putting =KillText(A1) in cell B1 and all I get is #VALUE! Can anybody see what I'm doing wrong please?........or have a better suggestion? Function KillText(cellinput) As String Set cellinput = Intersect(cellinput.Parent.UsedRange, cellinput) For i = 1 To Len(cellinput) Select Case Mid(cellinput, i, 1) Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 0, " ", "-", "/","%" charval = Mid(cellinput, i, 1) Case Else charval = "" End Select KillText = KillText & charval Next i End Function TIA Vaya con Dios, Chuck, CABGx3 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is there a way to delete all characters to the right of a ":"? | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
Excel "Move or Copy" and "Delete" sheet functions | Excel Worksheet Functions | |||
Adding "New" "Insert" "Delete" into a workbook to change from data 1 to data 2 etc | Excel Programming | |||
Delete characters up to ":" | Excel Programming |