get rid of hidden characters in Excel 2012
I have a text field which I use to look up on
when I look at the cell in excel it shows as 08/09/200907/09/2010
however if I copy this into here or into Notepad ++ it shows as
"05/09/2009 04/09/2010"
How do I get rid of these hidden characters in excel as I need to text to read 05/09/2009 04/09/2010 in the cell
I am using this as a master lookup in sql so have over 2000 records that don't match !
when I look at the cell in excel it shows as 08/09/200907/09/2010
however if I copy this into here or into Notepad ++ it shows as
"05/09/2009 04/09/2010"
How do I get rid of these hidden characters in excel as I need to text to read 05/09/2009 04/09/2010 in the cell
I am using this as a master lookup in sql so have over 2000 records that don't match !
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
this is NOT a date field but a text field that has the hidden characters. I've attached an excel example of what I have of the offending column!!! see example.xlsx below thanks
Hi, I think the attachment got lost. Did you click Embed?
ASKER
Hi,
This formula shows the ASCII code of the 11th character =CODE(MID(A3,11,1)).
It returns 9, which is TAB; this is what you are seeing in Notepad.
My formula above joins the first 10 and last 10 characters from the string, therefore eliminating the tab.
John
This formula shows the ASCII code of the 11th character =CODE(MID(A3,11,1)).
It returns 9, which is TAB; this is what you are seeing in Notepad.
My formula above joins the first 10 and last 10 characters from the string, therefore eliminating the tab.
John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
From my comment above, the ASCII code of the offending item is 9, not 160.
To make @ Patrick_Matthews formula dynamic, you can use this:
=CLEAN(SUBSTITUTE(A1,CODE(
It will remove whatever the 11 character is.
or else my original post, extracting first and last 10 chars.
John
From my comment above, the ASCII code of the offending item is 9, not 160.
To make @ Patrick_Matthews formula dynamic, you can use this:
=CLEAN(SUBSTITUTE(A1,CODE(
It will remove whatever the 11 character is.
or else my original post, extracting first and last 10 chars.
John
I realize that, John. CLEAN removes the tab (9), carriage return (13) and line feed (10), plus some other characters.
The 160 is to remove the non-breaking space, which CLEAN will leave untouched (presumably because CLEAN predates widespread use of WWW, which is how non-breaking spaces usually find their way into Excel worksheets via copy-paste).
The 160 is to remove the non-breaking space, which CLEAN will leave untouched (presumably because CLEAN predates widespread use of WWW, which is how non-breaking spaces usually find their way into Excel worksheets via copy-paste).
Thanks Patrick, I was not aware of that.
John
John
ASKER
didn't really sort the overall problem but got round it by some of the answers here
=LEFT(A1,10)&" "&RIGHT(A1,10)
Now in-between the two "" there are ten spaces. You can put in as much as you prefer...
Hope this helps and have a nice day