replace

[clux]

I have a character in an Excel file I need to replace, but can't find it as a
symbol or special character. Copying it into the search replace function only
gives a space instead of the character. The character is a box with a
question mark in it. Has anyone run across this character?

 

[Gary''s Student]

It is probably an ASCII 0129.

1. click on a cell
2. while hold down the ALT key, touch 0129 on the numeric keypad
3. format the cell font to Calibri

 

[Jacob Skaria]

Click Start>Programs>Accessories>System Tools>click 'Character Map' match the
character found and check out the keystroke shown in the status bar of
'Character Map tool'

If this post helps click Yes

 

[Francis]

Hi
try this :
1) select the cell
2) right click
3) select Format cells
4) going to Font tab

It will show you the type of Font in use for the cell, click ok
then look at the formula bar which will give you the character
the combination of these will produce the result you are seeking
--
Hope this help

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

 

[David Biddulph]

=CODE(...) will let you identify a character.
=MID(...) [or LEFT or RIGHT] will let you isolate one character from a
string.

 

[Dave Peterson]

Saved from a previous post.

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

Depending on what that character is, you may be able to use alt-#### (from the
number keypad) to enter the character into the Other box in the text to columns
wizard dialog.

In fact, you may be able to select the character (in the formula bar), and copy
it. Then use ctrl-v to paste into that text to columns Other box.

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 (or ctrl-j) 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(##),"")

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(##)) '<--What showed up in CellView?

myGoodChars = Array("")

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:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)