What is this Strange Characet? Find/Replace

  • Thread starter Thread starter D
  • Start date Start date
D

D

Hey guys-
Got a CSV file here that has an address field using a weird character to
separate the address line from the city/state/zip. The character is a square
box. I'm trying to do a find/replace on the thing, but, I don't know what it
is to be able to tell excel to find it. I've tried to copy/paste it into the
Find/Replace search box, but it won't recognize it. Can someone tell me what
this character is (is it a 'return' signal, a tab, what?) so that I can
replace it with a normal character in excel?
Thanks!
D
 
Chip Pearson has an addin that can help you find out what is exactly in that
cell.
http://www.cpearson.com/excel/CellView.htm

If it turns out to be "nice", you can use Edit|Replace
what: alt-xxxx (use the numbers on the number keypad--not above the
QWERTY keys)
with: (spacebar) or whatever you want.

This can work nicely with alt-enters (alt-0010), but will fail with other
characters (alt-0013 for example).

You could use a macro to clean them up:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(yy), Chr(zz))

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

End Sub

Change the yy/zz to what Chip shows (and you can drop ", chr(zz) if you only
have one offending character).

(And I changed them to space characters.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
BTW- I did a =CHAR(A1) on it and it comes back as 11.

So how do I do a find/replace on this thing now?
Thanks!
D
 
There are a couple of ways you could try to figure this out. The squar
character shows up when the character's number doesn't have
corresponding symbol (like a letter) defined in the font. Maybe yo
could try a couple of other fonts in your text editor to see if i
changes into something that you can then figure out. Another option i
to run your file through a little vb code that checks what the cha
number is at the position that this character appears in your file:

'myString has the text, and the character is at position 10
debug.print asc(mid(myString,10,1))

or something like that...
 
In the Find box, hold down the ALT key and type 0011 on the
numeric keypad (not the number keys above the letters).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
ok- tried doing Alt+0011 and it said it couldnt find anything to replace. I
also tried a Clean() function, and THAT DID work- but, I dont want to delete
the character since I need to find/replace and then go text to columns with
it. Any other ideas?
Thanks guys!
D
 
When I tried alt-0011, it didn't work.

You may end up using the macro.

You could use a helper column of cells with formulas:

=SUBSTITUTE(A1,CHAR(11)," ")

then drag down, copy|paste special|values over the original range and delete the
helper column of formulas.

(I'd try the macro--it looks quicker.)
 
Replace it with a character that isn't used. Maybe the vertical bar (|).

Then use that in your data|text to columns.

(I'd still use that other macro <bg>.)
 
ok- pasted it into word and it just moved down a line like I did a hard
return or something. Tried all different fonts and nothing.

I have no idea how to go about doing the VB route- any more hints on doing
that? Man this is getting to be a pain...
Thanks!
D
 
You could read David McRitchie's notes:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side. Paste the code
there.

Then hit alt-f11 to get back to excel.
then hit alt-f8 (or tools|macro|macros) and select the macro and click run.

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim iCtr As Long

myBadChars = Array(Chr(11))

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

End Sub

I replaced it with the vertical bar (|) in the code above.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find/Replace MSWord wildcard characters from within Excel 6
MS word Find and Replace using Regex - Help required 0
Square character(s) in cells 2
How to delete a "return" character (a "box") in cell of imported d 1
find and replace multiple values in one statement 3
Find and Replace text with formatting 3
Replacing characters 2
Find and replace "?" character 4

Back
Top