Square replace 3

[marcoman44]

In excel I have an imported a report that produced square symbols. The problem is that they cannot be replaced trough Edit/Find/Replace. I think they might be some type of carriage returns from the other report. I cannot do a sort and delete rows becuase of the format of the data. Any help is appreciated.

Thanks,

 

[SkipVought]

Hi,

Please copy and paste the symbol AS IS here.

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[marcoman44]

SkipVought,

I even put the symbol in here and it does not show up. It is a small square with a black border. I wrote a macro that deletes all empty rows but it does not delete this row. There are over 2,000 rows with this symbol, but again I cannot sort because the data is in two rows.

Thanks,

Fred

 

[sawedoff]

Can you select that symbol if you edit the cell it's in (F2)? If so, you can probably copy it and paste it into the Find/replace dialog.

Sawedoff

 

[marcoman44]

Thanks, but it does not recognize the box. I can copy into a different cell but not inro find/replace.

 

[SkipVought]

PLEASE PASTE IT HERE.

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[SkipVought]

copy the entire phrase including the character in question.

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[sawedoff]

This may be too simple, but what font is used in the cell that contains the symbol? Maybe you can reformat the font, then use a find /replace to get rid of it.

Sawedoff

 

[marcoman44]

I put the character in to paste and hit preview and it automatically goes away. It is a single character in about 1000 rows column A.

 

[sawedoff]

If you copy and paste the cell, does the symbol paste into a new cell? If no, copy column A and paste it into a new column, then delete A. (again, this seems too easy.)

Sawedoff

 

[marcoman44]

Yes the character copies over as well.

 

[longhair]

marcoman44,

do a copy (ctrl-c) go to this website:

http://www.cplusplus.com/doc/papers/ascii.html

put it in the ascii field - what does it give you in the dec field?
hth
regards,
longhair

 

[marcoman44]

NaN

 

[marcoman44]


I put the symbol in the Ascii and I got -1

 

[longhair]

marcoman44,
what this is, is an unprintable character. i was hoping we'd be able to if it's equivalent but no luck:
try this code in a macro make sure you are in a cell with the character:

dim test
test = acticell.value
msgbox(test)

does it give the same thing?
regards,
longhair

 

[sawedoff]

Try using =CLEAN(A1) where A1 is one of the cells containing the character. If it works, just copy it down, then do a copy, paste special-values to overwrite column A.

Sawedoff

 

[marcoman44]

Sawedoff,

the other fields are date/time. Column A is a date but at the start of a new page it gives this character. The Clean works but it messes up the date when dragged.

Thanks,

 

[longhair]

marcoman44,
is this the only character in the cell or part of a string?
regards,
longhair

 

[marcoman44]

It is the only character in the cell

 

[sawedoff]

You should be able to reformat the new column and bring back your date format after doing the clean. Try selecting the new column, Format - Cells, date and select the one you like.

Sawedoff