Find/Replace not working in cell with a lot of text

[JugglerJAF]

I have a cell containing 9,722 characters which definitely contains the text string "<span style=" followed by some other text and ending with the ">" character, but when I do a find a Replace (or a just a find) on "<span style=*>", Excel isn't finding and replacing the string.

I know that the text is there because I've copied the cell into Notepad and I can find it in that App using Ctrl+F.

The initial "<" character is at position 7,992 in the cell: found using =SEARCH("<",D2)

Is there a limit on how "deep" Excel can look into a cell with a lot of text. or is there some solution I can use to replace these unwanted values (which are HTML tags shown as code in a CSV file from a data export).

 

[Micron]

Is this vba or a sheet formula where you're using Replace? If vba, post your code (but please use vba code tags - vba button on posting toolbar). IIRC, Excel may impose a character limit on workbook functions (255) but vba has no such limit outside of the number of characters a string can hold. That number is around 2 billion.
In my testing, the found position is higher than 13 thousand but this is vba:

?instr(sheets("002").range("N3"),"<span style=")
13201

 

[JugglerJAF]

Is this vba or a sheet formula where you're using Replace? If vba, post your code (but please use vba code tags - vba button on posting toolbar). IIRC, Excel may impose a character limit on workbook functions (255) but vba has no such limit outside of the number of characters a string can hold. That number is around 2 billion.
In my testing, the found position is higher than 13 thousand but this is vba:

?instr(sheets("002").range("N3"),"<span style=")
13201

Thanks for your response. I'm trying to replace HTML codes from a CSV file that comes from a system download and the code I've used (for the majority of cases, but edge cases are all essentially the same apart from the replacement value) is as follows:

Selection.Replace what:="<*>", Replacement:="", LookAT:=xlPart, MatchCase:=False

This does replace HTML codes, but it doesn't work on cells containing a large number of characters and where the HTML characters appear "deep" in the cell text.

I did try using

Application.WorksheetFunction.Substitute

, but that option doesn't allow the use of wildcards which I need to have in order to pick up all HTML codes.

 

[Micron]

You're using Excel Replace method, not vba Replace function. I took a guess and I based my answer on the function (which AFAIK, does not accept wildcards).
Sorry, I don't know the answer about text limit for the method. In absence of an answer to that I guess I'd start with a cell where it doesn't work, strip it down to 250 characters and test again. If it works, you know that the method works with the smaller content you're trying to edit and to rule out that there isn't something different about the particular cell(s) where it doesn't work. Then I'd try again on various lengths to see where it stops working in an attempt to confirm it is all about the length.

You might be able to work around this with a procedure that uses a mix of string functions (Instr, InstRev, Len, Mid, etc.) with or without Replace, but without seeing string examples it's impossible to make focused suggestions. In the meantime, you should probably wait to see if you get a solution here. If anyone confirms there is a character limit with the method, my guess is that you will still need a work around.

 

[JugglerJAF]

You're using Excel Replace method, not vba Replace function. I took a guess and I based my answer on the function (which AFAIK, does not accept wildcards).
Sorry, I don't know the answer about text limit for the method. In absence of an answer to that I guess I'd start with a cell where it doesn't work, strip it down to 250 characters and test again. If it works, you know that the method works with the smaller content you're trying to edit and to rule out that there isn't something different about the particular cell(s) where it doesn't work. Then I'd try again on various lengths to see where it stops working in an attempt to confirm it is all about the length.

You might be able to work around this with a procedure that uses a mix of string functions (Instr, InstRev, Len, Mid, etc.) with or without Replace, but without seeing string examples it's impossible to make focused suggestions. In the meantime, you should probably wait to see if you get a solution here. If anyone confirms there is a character limit with the method, my guess is that you will still need a work around.

I've had a play around with some test data and found that the number of characters in the cell is not the limiting factor, but it's actually the position within the cell of the value being found/replaced. If the value being found/replaced appears in the first 8,188 characters, then it is found and replaced. It if appears at position 8,189 (or greater), then the code fails to find and replace the value.

I'm going to live with it for now as the number of records with text of 8,189 characters is limited, and I can easily find any that still contain the "<" character and amend those manually if they appear at all.

 

[Micron]

Interesting number. I expected the breaking point to be something along the lines of the numbers that coincide with binary numbers, such as 256, 512 etc. so that would have been 8192. If the number of instances proves to be an issue you can revisit this and perhaps approach it as I've suggested.