Macro to delete certain text within the excel sheet

[Dr3433]

I have a large amount of data that is imported on a weekly basis into a worksheet, due to the large amount of data, it has become overwhelming to look for certain text contained in a cell and delete.

I'm looking for someone who can help me with a macro that I can add into my excel sheet that will automatically delete certain text from the data but maintain the rest of the data. The data will continuely expand but the texts that I want removed will be the same. Is there a Macro for this?

Also, find and replace is not an option. I want the cell to remain blank.

 

[Rick Rothstein]

Also, find and replace is not an option. I want the cell to remain blank.

Can you explain this statement please?

 

[Dr3433]

Can you explain this statement please?

Don't want to use the Find and replace option in excel as mentioned earlier.

 

[Rick Rothstein]

Don't want to use the Find and replace option in excel as mentioned earlier.

I know you said that, but you followed it up by saying "I want the cell to remain blank"... what did you mean by that? You are asking about deleting text... so what would remain blank?

 

[Dr3433]

Hi Mr Rothstein,

Thanks for taking your time to answer my thread, I'm looking to delete certain text contained in the data worksheet. I am hoping that when the macro removes the text from the cell, the cell would be a "blank" cell, and all other data in the worksheet that did not contain the text would remain the same.

Basically I'm looking for a macro to read the worksheet and remove certain text words within the data while keeping the rest of the data intact. There are a few text words I want to eliminate and would like to use a macro to remove all those text words from the data. Is it possible to build a macro to perform this function?

 

[Piktro]

Hi Mr Rothstein,

Thanks for taking your time to answer my thread, I'm looking to delete certain text contained in the data worksheet. I am hoping that when the macro removes the text from the cell, the cell would be a "blank" cell, and all other data in the worksheet that did not contain the text would remain the same.

Basically I'm looking for a macro to read the worksheet and remove certain text words within the data while keeping the rest of the data intact. There are a few text words I want to eliminate and would like to use a macro to remove all those text words from the data. Is it possible to build a macro to perform this function?

You need to post sample data with expected results. You basically said "delete the data in a cell if it contains 'this text', but I want it to be blank, and I want to keep the rest intact."

Out of these options, which desired result would you prefer? I used the string "is" as an example.

[TABLE="width: 391"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Sample Data

[/TD]
[TD][/TD]
[TD]Desired Result

[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]this is sample data[/TD]
[TD] [/TD]
[TD][remove all data in cell if it contains string]

[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]this is sample data[/TD]
[TD] [/TD]
[TD]this sample data [remove exact match, no partial matches]

[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]this is sample data[/TD]
[TD] [/TD]
[TD]th sample data [remove all instances][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Further to Piktro's point, maybe you are looking for whole cell matches only?
So if the text of interest is "is" you may only want cell A2 cleared from my sample even though all 3 cells contain "is"?

Excel Workbook
	A
1	This text is kept
2	is
3	This text isn't deleted
Delete

Also, whatever the answers to the previous questions are, if the text of interest is "is" do you also want "IS", "Is" and "iS" deleted?

 

[mylifeupgrade]

This seems like the right place to post this question. I am trying to replace shorthand abbreviations with the longhand word without altering the rest of the contents of my cells.

I'd like the solution for scenario 2 listed above... but that it would take any case (upper or lowercase).

Here is my macro as it currently stands:

Sub Sample()
Dim ws As Worksheet
Dim aCell As Range
Set ws = ThisWorkbook.Sheets("Sheet1")
With ws
Set aCell = .Columns(2).Find(What:="svr ", LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not aCell Is Nothing Then
aCell.Value = "server"
Else
MsgBox "svr not found"
End If
End With
End Sub


Unfortunately, this just replaces the contents of the cell completely instead of just the text I'm looking for.


I would deeply appreciate it if you could help me.


Joseph.