HARD RETURNS WITH EXCEL CELLS 2

[markfsi]

I have received a file in excel format which contains hard copy returns within the cells, these 'squares' are in different places throughout the different cells. Does anyone know how i can replace this square with a " - ". Naturally I have tried the Find Replace Option but am unable to reproduce the hard copy return 'square'. i have tried various copies and pasting. I have tried importing to access and trying there.

 

[KenWright]

Copy one of those cells into a blank sheet into A1. In A3 put the following and copy down:-

=CODE(MID($A$1,ROW()-2,1))

If you get a 10 or a 13 then that is your hard return. Caps start at 65 and increase, amd lower case starts at 97. Punctuation is generally 44 and above - Any low number below 20 is likely to be your problem.

In the Find & replace box, in Find hold down the ALT key and type 010 or 013 (Whichever you find) on the NUMBER pad at the right of your keyboard and then just let go of the ALT key - Don't worry if you can't see anything. Put a - in the replace with and then hit the button.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Zathras]

Replace does work, but it's not obvious.

First select the column(s) that you want to update and type Ctrl-H. (Or select Edit/Replace from the menu.)

Then, with the cursor in the "Find what" box, use the numeric keypad to type Alt-010 (Press and hold the alt key, then type in sequence zero, one, zero and release the alt key.) You won't see anything happen, but trust me.

Then. put the hyphen in the "Replace with" box and click "Replace All"