"Ž" Appearing in Excel??

[evilnick26]

Hello All!

I work with large amounts of data that needs to be cleaned, usually in the form of big Access databases that are then copy & pasted (~10K records at a time) into Excel.
The problem is that when I do this, sometimes I get a lot of "datadatadataŽ(then carriage return)datadatadata" etc. within a cell.
I found this thread:

http://www.tek-tips.com/gviewthread.cfm/lev2/3/lev3/18/pid/68/qid/255914

Which shows how to replace carriage returns but not the Ž symbol... I tried replacing carriage returns with a | and then "text to column"ing but the dialogue doesn't recognise the character immediately after the Ž as visible! (As far as I can tell!)

So my question is: How do I find and replace for the rogue Ž character?? It's no fun having to go through 11K records one by one!!

 

[xlbo]

You need to find the Char value of the character. Select one of these characters and paste it into a cell on its own - say A1. then use =CODE(A1) - this will give you the code number for that character. I think the one you want is 142 but I can't be sure because you may have a different data symbol map
An example formula to get rid of this symbol would be:
=REPLACE(A1,FIND(CHAR(142),A1),1,)
Obviously, this could be done in a code loop also
HTH
Geoff

 

[evilnick26]

Geoff,

Cheers for the quick response! I've tried doing that but I can't copy & paste the square character when it appears in Excel. I think it must be signifying a character rather than being a character itself, if that makes any sense?!

Any more ideas?

Nick.

 

[Hasit]

I would have suggested what Geoff has, so can't anymore. Although I do have a question: you state that copy and paste records into Excel. Have you tried extracting data directly from Access using MS Query?

 

[evilnick26]

Well, I'm not using a query or anything to get the data from Access (it was originally extractedn from a proprietary DB in the US and emailed as a zipped Access DB to me) I'm just highlighting rows in the table and then copy & pasting.

I've found quite a time-consuming 'dirty' work-around using the code in that thread I mentioned earlier but this only works if I've still got the original Access DB. I'm sure I'll run into this again one day and was just wondering if there was a recognised solution but it seems like it's njot even a recognised problem!!

Thanks again for the help!

Nick.

 

[xlbo]

Nick - if you have a cell with the character in, can you not isolate it by using MID(CellRef,Startpoint,NoOfChars)
and then use the code function on that new cell to return the character code ????
you wouldn't need to copy it then
I have a sneaky suspicion that it is a representation of the line break code.......2 mins later - yup I think it definitley is.
In excel,in A1 type abcd press alt&return then type efgh
you should see, in one cell:
abcd
efgh
in the cell next to it, type =A1
you should see abcd "little square" efgh
in C1, enter = MID(B1,5,1)
The result should be a little square on its own
In D1 enter = CODE(C1)
the answer should be 10
Use
=REPLACE(A1,FIND(CHAR(10),A1),1,)
I thought it was 142 as what appeared here on HTML was a Z with an accent
HTH
Geoff

 

[evilnick26]

You're right! That does work but it gives a different square thing!! I'll try to explain; the one I got using your method (although imitating the original type cunningly!) does indicate a carriage return but the type I'm on about is the character *before* the carriage return.

With the original square character, if you select the cell then you see the square character in the Formula Bar. With the method you described, it's gone!

Okay, I'll try doing this on one of the character in *my* data... I get a code of 13

So if I modify the code from

http://www.tek-tips.com/gviewthread.cfm/lev2/3/lev3/18/pid/68/qid/255914

To replace character no. 13 (rather than 10 - proving, as both of us suspected that the square *can* indicate a carriage return) then I should be in business!!

And it works!

Brillsville, thanks for all the help. Much appreciated by me and the other guy who's got SO much data cleaning to do!

Nick.

 

[xlhelp]

You could try using the clean function.

=TRIM(CLEAN(CELL REFERENCE))

If this is imported text, then it might be a good idea to apply the formulas in a blank sheet and then paste all the values once you are done.

HTH

Indu