Text data pulled to excel shows small rectangle characters

[irfananeeza]

Folks, I am experiencing a very bizarre problem.

I have data being extracted from a text file to excel via a macro. However, when data gets pulled to the excel file it also shows small rectangle characters along with data.

I am not sure how to get rid of those small rectangle signs. Trim function does not work.

Any idea how to get rid of them via a macro.?

Thanks,

 

[F&s anaylst]

don't really what they are (embedded csv format??) not if select toolbars - control toolbox - Design Mode, you will be able to select these manually and delete accordingly

 

[sanrv1f]

where is it located within the cell? at the beginning or in the middle or at the end?

try one of these to identify what that char is,

if at the beginning,

=CODE(LEFT(A1,1))

if at end

=CODE(RIGHT(A1,1))

you will get a numeric code (ASCII),

now Ctrl + H (Replace)

in find what -> Alt + 0 & the char code you got
Replace with blanks

-> Replace All

 

[irfananeeza]

Thanks to both for the answer.

First of all, I checked the code character by using =code(right(b3,1)) and it comes up as 9. The characters are showing up on the right side. I did Cntrl+H and then in the "FIND WHAT" box I tried to do Alt+0 9 but it did not work. Rather, when I press Alt and while pressing Alt I press 0 and then 9 nothing comes up in the FIND WHAT box. So not sure what am I doing wrong here. Please clarify.

Secondly, it would be great if I can find a little code or a macro solution which I can add in my existing macro to get rid of this problem. I have tested my macro on Excel 2002 & 2003 versions. Both show me the rectangles when I extract data using this macro.

rgds,

 

[jolivanes]

Have a look here.

http://www.mrexcel.com/forum/showthread.php?t=294283

HTH
John

 

[sanrv1f]

instead of Alt + 09, try Alt+9