Any Excel gurus?

[PNWbrit]

I have an excel doc several thousand lines long. It's the output from a website that needs to have mass edits. One of the columns is the page text. Included in that text is some code that creates a form for visitors to submit (about 900 characters long) and some other code that inserts unwanted line breaks.

It's simple to replace the the short line break code with find and replace.

But the form code exceeds the 255(?) character limit for find and replace.

I've tried l searching for VBA code todo this but not really finding anything suitable. Maybe there's an excel add that overcomes this?

[scmartin69]

Not totally clear what you're asking? Do you want to remove the line breaks?
Regardless, export to CSV and use python to manipulate whatever you want and then re-import to excel? You can probably find a library that will work directly on excel, but sometimes it's not easy to work w/ MS formatted docs.

Sent from my SM-G975U using Tapatalk

[nwslider]

Is the character limit for the Find using ctrl + F or the find function set up in a cell?

I generally don't use excel with text strings very often so I'm not certain this will work. You could try using the FIND function nested in the REPLACE function set up in the cells. Not sure if there are character limits on the functions used in this manner.

FIND syntax: =FIND (find_text, within_text, [start_num])

REPLACE syntax: =REPLACE (old_text, start_num, num_chars, new_text)

Links with more detail:

https://exceljet.net/excel-functions...-find-function

https://exceljet.net/excel-functions...place-function

[PNWbrit]

Yep the line break code is unwanted in the place this is going - and find and replace does that easily.

it's the 1000 characters of the form code that I want to remove in one hit.

[toast2266]

Could you just use a simple if/then formula? If [cell with 1000 characters] = [current code] then [replace it with code that you actually want in there]

[nwslider]

Could you just use a simple if/then formula? If [cell with 1000 characters] = [current code] then [replace it with code that you actually want in there]

That should work if the only text in the target cell is the unwanted code.

The solution kinda depends on how the text is set up in the cells. If the unwanted code is split between cells then it gets more complicated.

[PNWbrit]

Yep there's text in there I need to keep. The actual page content.

[nwslider]

Is all the text in one cell? And do you know the exact code you are trying to remove?

[toast2266]

And is the unwanted text (or the text to be retained) the same in every cell?

You could maybe get to where you need with a series of formulas that break apart the long text string, and put pieces of it into other cells. Then have a formula that re-compiles it into the text string that you actually need.

[Skistack]

Is the spacing consistent in each row? You could use MID to extract just the page content.


Sent from my iPhone using TGR Forums

[nwslider]

As long a you know the exact text to be removed and it is all contained in one cell (the unwanted code can be duplicated in many cells as long as it isn't split between cells) a nested find/replace formula for each cell containing text should work.

Waiting for the excel heavy hitters to enter the thread...

[toast2266]

Also, I believe using ^c in the "replace" field of a standard find / replace will pull data from the clipboard, which doesn't have a 255 character limit. So you type out your replacement text in wordpad or whatever, copy it, then put ^c in the replace field in excel, and that should work.

[PNWbrit]

Also, I believe using ^c in the "replace" field of a standard find / replace will pull data from the clipboard, which doesn't have a 255 character limit. So you type out your replacement text in wordpad or whatever, copy it, then put ^c in the replace field in excel, and that should work.

Yep.

That doesn't seem to work in the find field though.

[XavierD]

As mentioned export to python is your best bet.

If you want to muscle through excel:
What’s the code in?
There should be some sort of delineator that will separate the two sets of code from both each other and the free text. Determine this logic and use that to create a new column for the form code using text to columns. Then you can filter and replace as necessary.

[caulfield]

Any chance you're on a mac or have some powershell shit going? Dirty dirty way I'd do this is run: sed 's/form_stuff//g' < tgr_test.csv > tgr_test_modified.csv

where form_stuff is the text you want to get rid of. It gonna be a big ass oneliner* and you'll have to escape some characters, but 900 characters seems manageable.

If things like bash one-liners and escape characters are totally unfamiliar to you, then this was an unfair suggestion. But in that case, "use python" probably isn't helping either.

*bonus points if you can make a regular expression that matches the unwanted text and get it down to one unwrapped line

[Snow Dog]

Instead of replacing the code you could comment it out. Use two find/replaces to add a"<!--" tag at the beginning of the code and a "-->" tag at the end.

[caulfield]

oh... can you just find and replace in 4 chunks of 255? If the 900 chars of your form are unique, I'm assuming each quarter of them will be unique too.

[PNWbrit]

Python worked.

Thanks all.

[The Suit]

Python worked.

Thanks all.

Nice. Did you use Pandas? Pandas is the shit.