Character codes

[Mikey May]

I have exported a report with various fields as a V5 excel
document. My problem is that in one of the fields the
text contains a small box where a space should be and/or a
box at the end of the text.

I could do an Edit, Replace if I know what needs replacing.

Any ideas on how I could replace these with spaces or
ideally insert a column break if opening as a .txt or .csv?

Cheers

 

[Roger Govier]

Hi Mikey

Try
=SUBSTITUTE(A1,CHAR(12)," ")

 

[Mikey May]

Mmmmmmm...... Char(12) doesn't seem to work

-----Original Message-----
Hi Mikey

Try
=SUBSTITUTE(A1,CHAR(12)," ")

--
Regards
Roger Govier



.

 

[Roger Govier]

Hi Mikey

To find what the value for Char() that you should be using, try
=CODE(MID(A1,n,1)) where n represents the position of the square box within
your data.
Then use this value in the previous formula I gave.

 

[Mikey May]

Sorted now, Char 10 and 13......

cheers

-----Original Message-----
Mmmmmmm...... Char(12) doesn't seem to work


.

 

[Dave Peterson]

You got your answer, but Chip Pearson has a very nice addin that'll help in
situations like this:

http://www.cpearson.com/excel/CellView.htm

And you didn't ask how to insert other special symbols, but Chip has an nice
addin for that, too:
http://www.cpearson.com/excel/chars.htm
(look for symbolizer)

(Insert|symbol was added in xl2002)