How to find and replace special characters in vba

A

adr12345

Board Regular
Joined
Jan 19, 2015
Messages
67
Hi,
My requirement is to see if any special character is present in character string and replace it with given character.
This should be done in all cells in column, one by one using loop.

How to do this?
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
adr12345,

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?

3. What column are your strings in?

4. What is the first cell that contains a string in that column?

5. Can we see a list of the special characters, and, what they should be replaced with?

6. Can se see at least 10 of the strings?

7. Can we see what the strings will look like after the special characters have been replaced?


To start off, and, so that we can get it right on the first try:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot (NOT a graphic, or, picture, or, flat text) try one of the following:

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

Excel Jeanie
Download

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to (the BLUE link-->) Box Net ,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
Running Microsoft Excel 13, is there anyway to automatically setup a default that would automatically apply number formatting to a fix text-formatted number when it is copied into an Excel 13 worksheet?
 
Upvote 0
JKrzyzak,

Welcome to the MrExcel forum.

I do not think that your request fits in to this original thread.

I would suggest that you start a new thread for your question, and, if you think it helps, provide a link to any other thread as a reference.

Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.

In your New Thread include:
1. What version of Excel, and, Windows are you using?
2. Are you using a PC or a Mac?
3. a screenshot, NOT a picture/graphic, of the raw data, and, worksheet name
4. a screenshot, NOT a picture/graphic, of the results (manually formatted by you for the results you are looking for)

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
If you do not know how to install and how to use HTML Mr.Excel Maker
https://www.youtube.com/watch?v=JycvgGppxt0&feature=youtu.be

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here
 
Upvote 0
Try Sandeep's response in this thread(Quoted Below) -- http://www.mrexcel.com/forum/excel-questions/390458-remove-spaces-special-character.html

I am looking for a way to make that code smaller to replace all characters that are not

a b c d e f g h i j k l m n o p q r s t u v w x y z
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
0 1 2 3 4 5 6 7 8 9
/ – ? : ( ) . , ‘ +
Space

Any ideas?


Sandeep Warrier said:
The code below replaces the special characters with its equivalent english character. You could modify it to suit your needs.

Code: 
Sub RemoveSplChar()
Dim rCell As Range

    For Each rCell In ActiveSheet.Columns("A:A").SpecialCells(xlCellTypeConstants, xlTextValues).Cells
        With rCell
            .Value = Application.WorksheetFunction.Substitute(.Value, "Á", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "á", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ð", "D")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ð", "D")
            .Value = Application.WorksheetFunction.Substitute(.Value, "É", "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, "é", "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, "í", "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Í", "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ó", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ó", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ú", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ý", "Y")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ý", "y")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Þ", "Th")
            .Value = Application.WorksheetFunction.Substitute(.Value, "þ", "th")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Æ", "Ae")
            .Value = Application.WorksheetFunction.Substitute(.Value, "æ", "ae")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ø", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ø", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ö", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ä", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ä", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ü", "ü")
            .Value = Application.WorksheetFunction.Substitute(.Value, "À", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "à", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, "È", "è")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ì", "ì")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ò", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ù", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ç", "c")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ç", "C")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(350), "S")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(351), "s")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Â", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "â", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ê", "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ê", "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Î", "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, "î", "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ô", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ô", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Û", "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, "û", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ñ", "N")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Õ", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "õ", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ã", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ã", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(256), "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(257), "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(260), "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(261), "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(258), "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(259), "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(264), "C")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(265), "c")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(268), "C")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(269), "c")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(262), "C")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(263), "c")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(270), "D")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(271), "d")
            .Value = Application.WorksheetFunction.Substitute(.Value, "É", "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, "é", "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ë", "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ë", "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(280), "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(281), "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(274), "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(275), "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(278), "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(279), "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(282), "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(283), "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(284), "G")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(285), "g")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(286), "G")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(287), "g")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(292), "H")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(293), "h")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Í", "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, "í", "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ï", "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ï", "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(302), "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(303), "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(298), "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(299), "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(308), "J")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(309), "j")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(310), "K")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(311), "k")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(313), "L")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(314), "l")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(315), "L")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(316), "l")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(319), "L")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(320), "l")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(321), "L")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(322), "l")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(323), "N")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(324), "n")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(325), "N")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(326), "n")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(327), "N")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(328), "n")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ö", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ö", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Õ", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "õ", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(340), "R")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(341), "r")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(342), "R")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(343), "r")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(344), "R")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(345), "r")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(346), "S")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(347), "s")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(348), "S")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(349), "s")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(350), "S")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(351), "s")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(352), "S")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(353), "s")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(354), "T")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(355), "t")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(356), "T")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(357), "t")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ú", "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ú", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ü", "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ü", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(362), "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(363), "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(370), "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(371), "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(366), "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(367), "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(368), "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(369), "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ÿ", "Y")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ÿ", "y")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ß", "ss")
            .Value = Application.WorksheetFunction.Substitute(.Value, "œ", "oe")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(377), "Z")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(378), "z")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(379), "Z")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(380), "z")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(381), "Z")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(382), "z")
            .Value = Application.WorksheetFunction.Substitute(.Value, "’", "'")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(1028), "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, ", Ltd", " Ltd")
            .Value = Application.WorksheetFunction.Substitute(.Value, ", Inc", " Inc")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ltd", "Ltd.")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Inc", "Inc.")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Inc.orporated", "Incorporated")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Pvt", "Pvt.")
            .Value = Application.WorksheetFunction.Substitute(.Value, "PvtLtd", "Pvt Ltd")
            .Value = Application.WorksheetFunction.Substitute(.Value, "PLtd", "P. Ltd.")
            .Value = Application.WorksheetFunction.Substitute(.Value, "CoPvt", "Co Pvt")
            .Value = Application.WorksheetFunction.Substitute(.Value, " Co ", " Co. ")
            .Value = Application.WorksheetFunction.Substitute(.Value, "CoPvtLtd", "Co Pvt Ltd")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Co Pvt Ltd", "Co. Pvt. Ltd.")
            .Value = Application.WorksheetFunction.Substitute(.Value, "..", ".")
            .Value = Application.WorksheetFunction.Substitute(.Value, ",", ", ")
            .Value = Application.WorksheetFunction.Substitute(.Value, " .", ".")
            .Value = Application.WorksheetFunction.Substitute(.Value, " ,", ",")
            .Value = Application.WorksheetFunction.Substitute(.Value, ". ,", ".,")
            .Value = Application.WorksheetFunction.Substitute(.Value, "  ", " ")
            .Value = Application.WorksheetFunction.Substitute(.Value, "£", "Pounds Sterling")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ƒ", "f")
            .Value = Application.WorksheetFunction.Substitute(.Value, "½", "1/2")
            .Value = Application.WorksheetFunction.Substitute(.Value, "¼", "1/4")
            .Value = Application.WorksheetFunction.Substitute(.Value, "§", "Section")
            
        End With
                
    Next rCell

End Sub
Click to expand...

 
Upvote 0
Tobiasaw said:
I am looking for a way to make that code smaller to replace all characters that are not

a b c d e f g h i j k l m n o p q r s t u v w x y z
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
0 1 2 3 4 5 6 7 8 9
/ – ? : ( ) . , ‘ +
Space
Click to expand...

Hi Tobiasaw
Welcome to the board

Sorry, not clear, replace those characters with what?

Remark:

I posted once some code to convert latin letters with diacritics into the corresponding base letters.

Check posts #8 and #9, here:

Convert Symbols to letters


Simple code but big table (300+ characters)
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Excel VBA Help
Replies
6
Views
214
justincaza
J
M
  • Question
[Excel->Word] Filling in Word document adds unwanted special characters
Replies
3
Views
254
NdNoviceHlp
NdNoviceHlp
B
  • Question
VBA macro for removing entire alphabet some special characters
Replies
1
Views
235
BobMaccaroni
B
S
  • Solved
Using the Find function within VBE
Replies
5
Views
110
RoryA
RoryA
S
  • Question
VBA to ignore certain MAC Address values but process all others
Replies
1
Views
173
myall_blues
myall_blues

Forum statistics

Threads
1,214,376
Messages
6,119,175
Members
448,870
Latest member
max_pedreira

Share this page

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
Back
Top