Excel - How to detect symbol/special character code

Asked By JeffIngma on 26-Sep-07 12:10 PM
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 replied on 26-Sep-07 12:47 PM
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
Joe replied on 26-Sep-07 12:50 PM
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 replied on 26-Sep-07 12:55 PM
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
Joe replied on 26-Sep-07 01:08 PM
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 replied on 26-Sep-07 01:15 PM
=CODE(MID(A1,n,1)) where you are trying to find the code for the nth
character in cell A1.
--
David Biddulph
JeffIngma replied on 26-Sep-07 01:20 PM
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...."
RagDyer replied on 26-Sep-07 01:19 PM
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 ... !


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
Peo Sjoblom replied on 26-Sep-07 01:48 PM
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 replied on 26-Sep-07 01:53 PM
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> "Jeff Ingman" <JeffIngman@> wrote
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 replied on 26-Sep-07 01:55 PM
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 replied on 26-Sep-07 01:56 PM
13 is a carriage return.
--
David Biddulph
JeffIngma replied on 26-Sep-07 02:10 PM
James...

No this particular character will not copy/paste.
James Silverton replied on 26-Sep-07 02:17 PM
Jeff  wrote  on Wed, 26 Sep 2007 11:10:05 -0700:

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


??>>  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
JeffIngma replied on 26-Sep-07 02:56 PM
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 replied on 26-Sep-07 03:01 PM
Because alt-enter is actually 010.
Dave Peterson replied on 26-Sep-07 03:05 PM
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
Dave Peterson replied on 26-Sep-07 03:12 PM
And if you are 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.




--

Dave Peterson
Gord Dibben replied on 26-Sep-07 03:14 PM
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
JeffIngma replied on 26-Sep-07 03:41 PM
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