How to detect symbol/special character code

[Jeff Ingman]

I often find symbols in MS documents I'd like to remove or replace with
something else.

Is there a way to detect the underlying code of a symbol or special
character to use for "Search and Replace"?

 

[James Silverton]

Jeff wrote on Wed, 26 Sep 2007 09:10:05 -0700:

JI> I often find symbols in MS documents I'd like to remove or
JI> replace with something else.

JI> Is there a way to detect the underlying code of a symbol or
JI> special character to use for "Search and Replace"?

Can't you copy the character into the Find box?

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

 

[Joel]

there are methods for detecting special characters and symbols. when I
perform cut and pastes I sometimes find it is necessary to first paste the
data into notepad. then copy again from Notepad to final location.

I use this when copying within Word as well as between many applications
(not only MS). it is a good way of filtering data.

There are lots of special character that can be in the data. Probably too
many to be detected in a worksheet function. This task would be bettter
writing in VBA macro.

 

[Peo Sjoblom]

If you put the particular character in a cell then use a formula like

=CODE(A1)

you will get the number for the character set for your computer, so if you
have a space it will return 32

=CHAR(32)

will return a space

so if you want to replace CHAR(32) do ctrl + h, then in the find what box
hold down the alt key while typing 032 on the numpad and the leave replace
with blank the space(s) should be gone



--


Regards,


Peo Sjoblom

 

[Joel]

Yes you can put the character into a find. I also do this a lot and replace
with nothing. There are lot of different special characters and rather than
search and replace I start with going to notepad because it will get rid of
multiple different types of special character in one step.

You asked if it can be detected.

 

[David Biddulph]

=CODE(MID(A1,n,1)) where you are trying to find the code for the nth
character in cell A1.

 

[RagDyer]

You're referring to *MS* documents in your post.

Since this is an XL group, this is what I do to find the code in XL sheets:

=Code(A1)

Will return the code for the *first* character in the cell A1, whether
visible or invisible.

If you know, or suspect a character (invisible) is elsewhere in the cell,
you can use something like this:

=CODE(MID(A1,2,1))
Where you're referencing the *second* character, or

=CODE(MID(A1,3,1))
Where you're referencing the *third* character,
And so on ... !

 

[Peo Sjoblom]

Interesting, it works for any other characters like char(10) (carriage
return)
Anyway if you do this on a regular basis you'd be better of using code,

Sub RemoveChr13()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Selection.SpecialCells(xlConstants).Replace What:=Chr(13), _
Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Instructions on how to install macros

http://www.mvps.org/dmcritchie/excel/install.htm



--


Regards,


Peo Sjoblom

 

[James Silverton]

David wrote on Wed, 26 Sep 2007 18:15:09 +0100:

DB> =CODE(MID(A1,n,1)) where you are trying to find the code
DB> for the nth character in cell A1.
DB> --
DB> David Biddulph

DB> in message
??>> I often find symbols in MS documents I'd like to remove or
??>> replace with something else.
??>>
??>> Is there a way to detect the underlying code of a symbol
??>> or special character to use for "Search and Replace"?

My earlier answer was to the question of *replacing* an unknown
character. If you want to know what actually was the unknown, I
don't know an answer offhand. CODE(MID(A1,1,1), say, in Excel
2002 will not always work. To take a simple example, I inserted
the Russian character "yah" (R written backwards), 042F in Word,
copied it and pasted it into Excel. CODE(MID(A1,1,1)) gave 63.
I'm not sure what that means since it's not even
MOD(HEX2DEC("042F"),256) as I might have suspected.



James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

 

[Peo Sjoblom]

Also

=CLEAN(A1)

removes those characters, so you can use that formula then paste special as
values over the old data

--


Regards,


Peo Sjoblom

 

[David Biddulph]

13 is a carriage return.
--
David Biddulph

Thank you for your post Peo.

I tried your suggestion and got "13" as the code for the symbol I wanted
to
remove. I opened the search/replace box and typed Alt 013 and hit Replace
All. I got an error message saying "Microsoft Office Excel could not find
any
data to replace...."

 

[Jeff Ingman]

James...

No this particular character will not copy/paste.

 

[James Silverton]

Jeff wrote on Wed, 26 Sep 2007 11:10:05 -0700:

JI> No this particular character will not copy/paste.

JI> "James Silverton" wrote:

??>> Jeff wrote on Wed, 26 Sep 2007 09:10:05 -0700:
??>>
JI>>> I often find symbols in MS documents I'd like to remove
JI>>> or replace with something else.
??>>
JI>>> Is there a way to detect the underlying code of a symbol
JI>>> or special character to use for "Search and Replace"?
??>>
??>> Can't you copy the character into the Find box?

If you can't copy or paste it, you've got me beat I think of
a way to set up an example to experiment on.

Good luck!

James Silverton
Potomac, Maryland

E-mail, with obvious alterations:
not.jim.silverton.at.verizon.not

 

[Jeff Ingman]

One of the other posters here indicated that "013" is a carrige return. Now I
don't know why it is displaying in a cell as a symbol. I can create carrige
returns in cells by typing "Alt/Enter" and the symbol does not appear when I
click inside a cell.

Why does the symbol appear visibly sometimes but not others?

And why can't I loose it by typing "^013" in the replace box?

jeff

 

[Bob I]

Because alt-enter is actually 010.

One of the other posters here indicated that "013" is a carrige return. Now I
don't know why it is displaying in a cell as a symbol. I can create carrige
returns in cells by typing "Alt/Enter" and the symbol does not appear when I
click inside a cell.

Why does the symbol appear visibly sometimes but not others?

And why can't I loose it by typing "^013" in the replace box?

jeff

:

 

[Dave Peterson]

If you're only looking at excel...

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

You may be able to use Edit|Replace to change the character--Some characters can
be entered by holding the alt-key and typing the hex number on the numeric
keypad. For example, alt-0010 can be used for linefeeds. But I've never been
able to get alt-0013 to work for carriage returns.

Another alternative is to fix it via a formula:

=substitute(a1,char(##),"")
or
=substitute(a1,char(##)," ")

Replace ## with the ASCII value you see in Chip's addin.

Or you could use a macro (after using Chip's CellView addin):

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(##), Chr(##)) '<--What showed up in CellView?

myGoodChars = Array(" ","") '<--what's the new character, "" for nothing?

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Dave Peterson]

And if you're really trying to replace alt-enters in the edit|replace dialog,
you can type alt-0010 (from the numeric keypad) or you can use ctrl-j.

 

[Gord Dibben]

When using Alt + Enter, the linefeed is the 0010 character, not the 0013.

0013 is very hard to get rid of except through VBA or the CLEAN function.


Gord Dibben MS Excel MVP

 

[Jeff Ingman]

Dave...

Thanks a bunch to you... and the other responders to this string.

As practical matter... this problem proved more complicated than I
originally imagined. It took me about 3 minutes to manually remove the
offending symbols... and I may not run into this particular problem again.

I've done a few macros in Excel... and could probably follow your
instructions... but it would take more time than it is worth to me.

But thanks a bunch regardless.

jeff