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

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

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