Hello everyone,
I've imported an excel spreadsheet from Google Webmaster Tools and all the information ends with a line break.
Does anyone know how to remove hidden characters such as a line break from Excel 2010 at please?
It is essential for the work I'm doing.
Any help, is greatly appreciated.
Thanks
James
Hi James
1. Press Ctrl+F to display the Find tab of the Find and Replace dialog box.
2. In the Find What box, hold down the Alt key as you type 0010 on the numeric keypad. It may not look like anything is in the Find What box, but the character is there.
3. Click Replace All.
Tony
what is the character code? use =code(right(a1)) to get it
you may be able to find replace in bulk bulk using that code by using find/replace find hold down alt key and type it as a 3 digit code adding a leading 0 if needed but you must use the number keypad (,its a bit hit and miss as it sometimes stops working )
replace leave that blank hit return
alternatively you can try =clean(a1) fill down then copy pasteback as paste/ special/ values
or if that doesnt work you can try =SUBSTITUTE(A1,CHAR(10),"") where 10 corresponds with whatever code you discovered.
or you could try text to columns and see if you can strip it off there,(just select column data/text to columns ,fixed width double click any breaks to remove them click finish) but to be sure we need to see a sample
Last edited by martindwilson; 05-09-2013 at 10:26 AM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hi Tony,
Sorry I've tried this and it hasn't worked.
It is the line break which appears in the code.
no good just saying its the line break ,what is its ascii value?
It looks like a HTML character ¶ - I've looked on the ascii and it looks like it doesn't have one.
That's a paragraph sign, or "Pilcrow". Ascii #244. HTML: ¶ ¶
Can you post an example of the data that you are trying to modify to remove that symbol?
Last edited by BoardGuy; 05-09-2013 at 11:32 AM.
Did you use code(right(a1)) to get the code
urls.xlsx
Hi there, attached is the spreadsheet this is URLs from Google Webmaster Tools.
When I copy and paste the URLs into Word there are hidden paragraphs.
I've tried the suggestions recommended above.
Thanks for all your help throughout this submission.
Many thanks
cant see anything when i open it all looks fine and seems ok when pasted into word
¶ shows as char 182
have you tried copying it then paste into the find box of find/replace
Last edited by martindwilson; 05-09-2013 at 12:28 PM.
Thanks for your help.
The reason I require the characters to be hidden is so that the URLs go directly into a htacess file. This is what a website server reads. I've been told that hidden characters cause break the website and it's happened before.
I've been following this tutorial: http://blog.search-mojo.com/2010/10/...4-quick-steps/
It's worked fine in the past few weeks. But since, I've copied and pasted the source from Google Webmaster tools on this occasion it appears to include hidden characters.
It might be worth it to just throw the text into notepad. Since there are not any filters or other settings, that will let you see any unexpected characters that are present. In Excel, I'm not seeing any symbols or extra chars. In Word, I'll get the paragraph markings only when symbols are turned on. I cannot select those characters to copy and paste, as they are added automatically by the program.
It won't be much fun, but worst case, since it looks like most of your sites have the same domain, maybe just put the domain in one column, put the site path for each individual page in another column and CONCATENATE the two together.
Removed ......
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks