Find Multiple Texts and Replace Texts / Blanks within a List of Texts

[ibuhary]

Hi All,

I am looking for Excel Simple VBA Code to find multiple texts withing my data and replace with another text or blank (means delete).

I have two sheets in the attached sample workbook.

1. Data - this is where I have my data where I want to do the find and replace.

2. Words Edit - In this sheet I have words in Column - G these words we need to find in the Data and Replace with Words in Column - H, if Column H is blank it means the word in Column - G needs to be found and deleted in the data.

The Lists in Words Edit sheet to find starts with a space and ends with a space, this has been done to ensure that we would find and replace only whole words and not part of a word.

e.g the first record in the Data Sheet:
" SAM 1X 5059X DS 16GB - Black DE "

The result should be - " Samsung 1X 5059X Dual Sim 16GB - Black "

[Bo_Ry]

Please try

Please Login or Register  to view this content.

[ibuhary]

Thank you for your response.

I have tried the code. The results are satisfactory.

I just did a small enhancement to the below line:

c.Value = " " & c.Value & " " (so that we have a space in front as well as at the end)

Thank you again and I will accept your response as the answer to my question.

[ibuhary]

Bo,

I am experiencing some issues with the highlighted (in yellow) range in the attached file (Sample2).
Seems in this range there are still words not replaced or deleted.
I am unable to find the reason.

Can you please check.

[Bo_Ry]

The space in highlighted is chr(160) (non-breaking space) not normal space chr(32)

Please add red line

Please Login or Register  to view this content.

[ibuhary]

Perfect

[ibuhary]

Can you tell me how to find the "Chr(160)" like number for any character in a string ? I am sorry to ask you after long break...

[Bo_Ry]

If you mean Ctrl+F

at any cell enter =CHAR(160) => press F9 => Press Ctrl+C => Press Ctrl+F => Press Ctrl+V => Click Find All

[ibuhary]

no, You didn't get my question.

I am not asking how to find the character in the sheet.

I am asking how you decide if its CHAR(160) or any other CHAR(???) is there a chart like this to refer to see what are all the Special Characters and the corresponding CHAR(#) listed.

[Bo_Ry]

Sorry about that, but that is what I do first to check if there any Char(160)

Please try at
J912
=MID($H912,COLUMNS($J912:J912),1)
J913
=CODE(J912)

Drag J912:J913 to the right

You will find 160 at AH913

[ibuhary]

Okay that was what I was looking for.

Thanks a lot for that info.

My colleague who is sitting in a different country is facing a problem when he run the macro. The special CHAR(160) does not get replaced in his PC.

Does this have to do anything with location ?

[Bo_Ry]

No, CHAR(160) could come from any other database, SQL, etc.