Question mark character?

**JoanneC** · 11-10-2011, 02:35 PM

Hello...

I am posting for the first time and hoping for some insight regarding an issue. I have an input file from a client formatted as an "xls" file. My company's application takes that file and processes it in order to return some additional information. We generate an output file that first just parrots back the input columns provided by the client. Generally that's a no brainer, but this time all of those cells have a little character after the text. I just tried to copy it here but it doesn't work. It's a little black diamond with a white question mark in it.

I don't know if it's a special hidden character. If so, I don't know how to get rid of it. It does not display in my input file.

I cannot return the file to the customer this way. Does anyone have any knowledge about this?

**pike** · 11-10-2011, 02:39 PM

Hi JoanneC,
you will find that the are new line markers
"Alt" + "Enter"
which splits data up in a cell into new line within the cell

**JoanneC** · 11-10-2011, 04:04 PM

Wow!! I am so glad to know that. So can I turn them off or make them not visible like paragraph and space indicators in Word? If I send the file with them visible, will they also be visible to the client?

Thanks so much for your help!

**NBVC** · 11-10-2011, 04:17 PM

See here...http://en.wikipedia.org/wiki/Unicode...ment_character

It seems to be an unknown character.. You may need to fix the text in the file before importing into excel

**jaslake** · 11-12-2011, 09:54 PM

Hi JoanneC
I've used this code in the past to "clean" non-printable characters...see if it works for you. Specify in the code the column(s) you wish to "clean".

Please Login or Register  to view this content.

**gothic chicken** · 10-19-2018, 12:27 AM

If pike is correct with "new line markers" then =SUBSTITUTE(A1,CHAR(10),"") will remove the trailing hard return (Alt+Enter).

If you're not sure what the character is though, =SUBSTITUTE(A1,RIGHT(A1,1),"") should cater for any odd characters at the end of the text. I'd use =trim(CLEAN(SUBSTITUTE(A1,RIGHT(A1,1),""))) just to make sure to catch everything, like trailing spaces.

I've just applied it to the string "Women photographers � New Zealand" using =SUBSTITUTE(A1,MID(A1,SEARCH("s ",A1)+1,3),"|") and got 'Women photographers|New Zealand". The black diamond "Replacement Character" was a substitute for a question mark [=CODE(63)] for some reason, but =SUBSTITUTE(A1,CHAR(63),"") would not remove it.