I have a range of data, this data includes articles with HTML codes. Each cell have one article and a total of 200 articles therefore 200 cells.
To explain in short, each cell have the word "<footer" in it. What I want to do is find the exact text "<footer" and replace it with, lets say any character "a" or "b" or whatever. But when I replace it only replaces 25 out 200, I am not sure what's wrong. Please help, I am badly stuck in this.
Please find the excel attached.
As far as I can see there are only 25 cells that contain "<footer".
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
You are correct, faizzsheikh. When you use find-replace, it only replaces 25. There must be something like a non-printing character there too. However, if you use a formula (see attached cols B and c) it replaces the lot.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
No... they're there. I checked with =FIND("<footer",A3), copied down...
@glenn kennedy
=SUBSTITUTE(A3,"<footer","|") this solves my problem! Thank you so much Glenn.
However, if its due to non printable character how can we fix it without using a formula?
God, I am out of trouble!!
I have been playing with this a bit more. It's VERY odd indeed...
No further forward. Why can't you use a formula to replace them?
strange
i put in cell a2
=repeat("x",row(a1)*100)&"<cat" dragged down to a131
copy paste back special values so only the text was in there
filter on contains cat shows all 130
however
now find/replace <cat with <dog it only did 81
"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
you have from 6129 to 24330 characters in each cell.
You will have to use something else to use, Possible Notebook or Notepad++
Last edited by davesexcel; 01-03-2015 at 12:24 PM.
I imported your Excel data into Word and did a Replace for <footer and there were 200 instances.
Excel will only find 25 instances. Use Word to do the Replace and copy back to Excel.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
When I do Find exact match xlvalues, it finds 200
when I do Replace, replace window does not have XLValues only xlformulas, and then it only replaces in 25 cells.
I think it may be a memory thing,
That makes me feel a bit better... if you're baffled. I've been searching for the "usual suspects amongst non-prnting characters, but can't find anything obvious.
it's something to do with the length of the strings. The strings can be up to 24,330 characters. Only the 25 shortest (<10492 characters) are replaced with CRTL & H. Is there a limit on the number of characers CTRL+H can cope with???
http://www.teachexcel.com/excel-help...o.php?i=604224
There used to be a 8192 limit. Must be more, now...
Using =FIND("<footer",A3) and filling down, finds <footer in every record. To replace <footer with something else use SUBSTITUTE
Enter in B3 and copy down then copy and Paste Values to get rid of the formula.
Formula:
Please Login or Register to view this content.
I'm not sure of the limitations, when I did a find replace loop, the workbook crashed.
There are no specifications listed for find, replace in the specifications.
I suggested that back at Post 3, but the OP seemed t want an alternative solution.Originally Posted by newdoverman
Using =FIND("<footer",A3) and filling down, finds <footer in every record. To replace <footer with something else use SUBSTITUTE
Enter in B3 and copy down then copy and Paste Values to get rid of the formula.
Formula:Please Login or Register to view this content.
This is extremely unusual... I experimented with 3 different cells which was not finding "<footer". I kept picking pieces of content and find it until exactly from where excel is rejecting to find, and to my surprise one had a character "a" another a space " " and another a character "p"
How is this possible!
as far as i can determine for find/ replace it's max 8192 character length(excel 2007)
see attached
Last edited by martindwilson; 01-03-2015 at 01:20 PM.
What you are suggesting will not work, I already tried before coming here. When you copy back the data from Word or Notepad++ to excel the data gets messed up.
My problem is already solved with the SUBSTITUTE formula suggested by Glenn Kennedy. I was just curious to know the reason why it was happening.
Hi, you're back... thisone's had us all baffled. It looks like the problem is related to the length of the string. Excel used to have an 8192 character limit for CTRL+H (see post 13). It's more than tha now (at least 10492: see post 12).
I had thought that you weren't able to use SUBSTITUTE, but it seems like you can. If that's the case, can you pleas mark the thread as solved?
Thanks a lot everyone for your help... you guys are wonderful!
The thread is marked 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