How to delete particular value in a cell without deleting existing data using find option

Natsha

New Member
Joined
May 20, 2021
Messages
43
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
I need an macro to delete the value which I find ...Example , I have data in coloum A and B, with header and data start in A2 and B2 and extend .
Starting from column B2 ,I have values like 1-6;3-8;12-6 ..etc. I eed an macro where I can remove particular value I type in textbox by find option and deleted it with deleting other data in the cella
Example B2 has 1-6;3-8;12-6;9-9 ,I want to remove ;9-9, after I paste the value in textbox it should remove ;9-9 and change into 1-6;3-8;12-6;

I Need macro where I can select the cells that datas need to be removed. Column B is the default column containing datas

Note: The value I need to delete changes everytime like, ;1-1 or 8-9; or 12-13;

kindly help.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,723
Office Version
  1. 365
Platform
  1. Windows
Example B2 has 1-6;3-8;12-6;9-9 ,I want to remove ;9-9, after I paste the value in textbox it should remove ;9-9 and change into 1-6;3-8;12-6;
Your example is not entirely clear. If ";9-9" is removed then there would be no semicolon remaining at the right end of the text.

To help clarify different possibilities, what should be left in the cell if it starts with the following and you put ";9-9" in the textbox?
9-9;5-4;9-9;8-4;9-91;9-9

You could also give some more examples of your own if you think that it would help clarify the requirement.
 

Natsha

New Member
Joined
May 20, 2021
Messages
43
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Your example is not entirely clear. If ";9-9" is removed then there would be no semicolon remaining at the right end of the text.

To help clarify different possibilities, what should be left in the cell if it starts with the following and you put ";9-9" in the textbox?
9-9;5-4;9-9;8-4;9-91;9-9

You could also give some more examples of your own if you think that it would help clarify the requirement.
Hi

Thanks for the reply

To make it clear, in a cell there is a data like this.. 1-1;2-2;3-3;4-4

If I want to remove 4-4 , I do find and replace..in find box I give ;4-4 and leave the replace box blank... Along 4-4 I need to the semicolon also get deleted.

In another example if I want to remove 1-1 , in find box I give 1-1; and in replace box blank so that 1-1 will get deleted along with the unwanted semicolon.


I need a macro .where which ever value I want to remove ,I can text it in the textbox or msg box and that value should be removed in the excel sheet...all data will be from b column starting from b2
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,711
See if the following works for you:
VBA Code:
Sub Test()
    Dim str As String, rng As Range
    str = ";" & InputBox("Enter text to be removed")
    If str = ";" Then Exit Sub
    Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
    Application.ScreenUpdating = False
    rng = Evaluate(""";""&" & rng.Address)
    rng.Replace str, "", xlPart, xlByRows, False
    rng = Evaluate("INDEX(REPLACE(" & rng.Address & ",1,1,""""),,)")
    Application.ScreenUpdating = True
End Sub
 

Natsha

New Member
Joined
May 20, 2021
Messages
43
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows

ADVERTISEMENT

See if the following works for you:
VBA Code:
Sub Test()
    Dim str As String, rng As Range
    str = ";" & InputBox("Enter text to be removed")
    If str = ";" Then Exit Sub
    Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
    Application.ScreenUpdating = False
    rng = Evaluate(""";""&" & rng.Address)
    rng.Replace str, "", xlPart, xlByRows, False
    rng = Evaluate("INDEX(REPLACE(" & rng.Address & ",1,1,""""),,)")
    Application.ScreenUpdating = True
End Sub
Hi it works but it's only removing values like 1-1; if it is the first value of the cell.. it's not removing values like this ;2-2... If the semicolon is after the value it's removes but if its before the value it's not removing, also it's not removing if the value is pasted inbetween.

I need an code where it should remove value like 1-1; or ;2-3 .. wherever it positioned... The value which I need to delete sometimes will be pasted first sometimes middle ,sometimes at the end.
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,711
The code offered in Post #4 has been tested to remove the required patterns located at the beginning, in the middle, and at the end of a text string.

Try the following modification -- it provides a better guidance on what needs to be entered.
VBA Code:
Sub Test()
    Dim str As String, rng As Range
    Do: str = ";" & InputBox("Enter text to be removed. Example: 1-2")
    Loop Until str Like ";#-#" Or str Like ";#-##" Or str Like ";##-#" Or str Like ";##-##" Or str = ";"
    If str = ";" Then Exit Sub
    Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
    Application.ScreenUpdating = False
    rng = Evaluate(""";""&" & rng.Address)
    rng.Replace str, "", xlPart, xlByRows, False
    rng = Evaluate("INDEX(REPLACE(" & rng.Address & ",1,1,""""),,)")
    Application.ScreenUpdating = True
End Sub
 

Natsha

New Member
Joined
May 20, 2021
Messages
43
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows

ADVERTISEMENT

The code offered in Post #4 has been tested to remove the required patterns located at the beginning, in the middle, and at the end of a text string.

Try the following modification -- it provides a better guidance on what needs to be entered.
VBA Code:
Sub Test()
    Dim str As String, rng As Range
    Do: str = ";" & InputBox("Enter text to be removed. Example: 1-2")
    Loop Until str Like ";#-#" Or str Like ";#-##" Or str Like ";##-#" Or str Like ";##-##" Or str = ";"
    If str = ";" Then Exit Sub
    Set rng = Range("B2", Range("B" & Rows.Count).End(xlUp))
    Application.ScreenUpdating = False
    rng = Evaluate(""";""&" & rng.Address)
    rng.Replace str, "", xlPart, xlByRows, False
    rng = Evaluate("INDEX(REPLACE(" & rng.Address & ",1,1,""""),,)")
    Application.ScreenUpdating = True
End Sub
Sorry, if didn't work it only delete if the value I need to delete is the first one.

To make it more clear
In a cell b2 ..I have data like 20-98; 6-3; 7-7;1-94
If I need to delete 20-98; it's deleting it ,but If I want to delete 6-3; an middle value or ;1-94 last value it's not deleting
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,549
Office Version
  1. 365
Platform
  1. Windows
Need macro where I can select the cells that datas need to be removed. Column B is the default column containing datas
Edited:
Select the range, run the code, type the keyword in the inputbox then OK.
VBA Code:
Sub a1183689a()

Dim c As Range
Dim sAddress As String, svar
   
    svar = Application.InputBox("Insert keyword:", Title, Type:=2)
   
    If svar = False Or svar = "" Then Exit Sub 'if user cancel
    With Selection
    Set c = .Find(What:=svar, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        sAddress = c.Address
        Do
           Set c = .FindNext(c)
           c = Replace(c, svar, "")
        Loop While Not c Is Nothing And c.Address <> sAddress
    Else
        MsgBox svar & " is not found"
    End If
    End With

End Sub
 

Natsha

New Member
Joined
May 20, 2021
Messages
43
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Edited:
Select the range, run the code, type the keyword in the inputbox then OK.
VBA Code:
Sub a1183689a()

Dim c As Range
Dim sAddress As String, svar
  
    svar = Application.InputBox("Insert keyword:", Title, Type:=2)
  
    If svar = False Or svar = "" Then Exit Sub 'if user cancel
    With Selection
    Set c = .Find(What:=svar, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not c Is Nothing Then
        sAddress = c.Address
        Do
           Set c = .FindNext(c)
           c = Replace(c, svar, "")
        Loop While Not c Is Nothing And c.Address <> sAddress
    Else
        MsgBox svar & " is not found"
    End If
    End With

End Sub
Works perfectly... thank you so much 🤗
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,549
Office Version
  1. 365
Platform
  1. Windows
Sorry, actually it could be simpler:
VBA Code:
Sub a1183689b()

Dim c As Range

    svar = Application.InputBox("Insert keyword:", Title, Type:=2)
   
    If svar = False Or svar = "" Then Exit Sub 'if user cancel
    Selection.Replace What:=svar, Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, _
    MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

End Sub
 
Solution

Forum statistics

Threads
1,147,632
Messages
5,742,232
Members
423,714
Latest member
ftp2jz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top