remove/delete characters

[vrunda]

I need a code to remove characters specified in text box on userform . I used replace function but the code takes the sequence of characters/text/digits given in textbox. I have seen a utility which deletes the characters mentioned in textbox in any order.

Suppose my words are : Ram# , (Seeta), {Krishna} etc. In text box if i give "#)e" then it shall delete all three char. from every word if present.

But by replace func. it is not doing so.

Pls help

 

[Luke M]

Instead of using REPLACE, use the SUBSTITUTE function. Arguments are text, old_text, new_text.

 

[vrunda]

It is working same as replace function. It deletes the text or characters or numbers if they are mentioned in textbox in same sequence as they appear in sheet cells.

Eg.:-

from

form

rofm

textbox value is of

then it deletes only from last word of ie. rofm becomes rm but other two words are same

from

form

 

[NARAYANK991]

Hi Vrunda ,


The MOREFUNC add-in does exactly what you want. Check it out here :


http://www.ashishmathur.com/tag/morefunc/


Narayan

 

[vrunda]

Thanks for reply.

I need a vba code to complete my macro/ add-in. Is it possible? I shall check the more func add-in too.. ..

Still the code will be more appreciable. Pls check the following::

Private Sub removechar()

Dim nstr As Variant

Dim rrange, rng As Range

Dim i As Integer

nstr = textbox1.value

Set rrange = 'Application.InputBox(prompt:="specify range", Type:=8)


For Each rng In rrange

For i = 1 To Len(nstr)

rng.Formula = Application.WorksheetFunction.Substitute(rng.Formula, nstr, "")


Next i

Next rng


End Sub

 

[NARAYANK991]

Hi Vrunda ,


Try this :

[pre]

Private Sub removechar()
Dim nstr As Variant
Dim rrange As Range, rng As Range
Dim i As Integer
nstr = TextBox1.Value
Set rrange = Application.InputBox(prompt:="specify range", Type:=8)

For Each cell In rrange
For i = 1 To Len(nstr)
curr_char = Mid(nstr, i, 1)
cell.Value = Application.WorksheetFunction.Substitute(cell.Value, curr_char, "")
Next
Next cell
End Sub

[/pre]
Narayan

 

[shrivallabha]

Select the range where you want to carry out replacement and then run this code.


Option Explicit

Public Sub TestCode()

Dim strReplace As String, strRepChar As String

Dim i As Integer

strReplace = Application.InputBox("Please Input Characters to Replace", Type:=2)

For i = 1 To Len(strReplace)

With Selection

Call .Replace(What:=Mid(strReplace, i, 1), Replacement:="", Lookat:=xlPart, MatchCase:=False)

End With

Next i

End Sub

 

[vrunda]

Thank you!!

Thanks alot

Both of YOU Narayan & Shrivallabha

Both codes work great. Tested both. Worked flawlessly.

Very much appreciable.

Thanks a million

 

[vrunda]

I analyse where i made mistake... Actually the mid function does it all. I 'll study it again