Search and replace in excel - data too long

[aceparts_com]

I have various bits of HTML in an excel spreadsheet and want to change a block of the text but the search and replace won't work as the 'Formula is too long'

Anyone got an idea how I can perform the operation?

[FunkyGibbon]

a bit of a faff but if it is a one off job:

cut and paste the excel sheet in to word as a table
do your search and replace
cut and paste the table back into excel

good luck

[aceparts_com]

PERFECT!!!!!!!!! Thanks

That's not faffing around at all compared to the alternative

I owe you a drink, or set of bulbs!

[aceparts_com]

Actually - technical hiccup!

When I cut the data from Excell it has 10 lines of HTML in one cell. When I paste it back in it's spread across 4 rows so doesn't fit...

[aceparts_com]

The issue is with some carriage returns in the HTML; Word is picking them up. Any idea how I remove the formatting?

[ATG]

If you do the search and replace in Word, and save the new text as a text file, you can use Excel's "import" method rather than its "Open" method to re-open the file in Excel. This gives you control of how Excel interprets the text and will allow you to selevt something like a fixed width format with a single very wide column.

[aceparts_com]

Thanks :O)

Cracked it!

[ATG]

aceparts_com said:

The issue is with some carriage returns in the HTML; Word is picking them up. Any idea how I remove the formatting?

Eugghh. That may be a biut tricky.

Just a thought ... have you tried doing your search and replace in more than one pass?

I.e. if you wanted to replace "AAAAABBBBB" with "EF" you might be able to search on "AAAAA" and replace it with "E" then search on "EBBBBB" and replace it with "EF" ... or countless other variations on that theme. Clearly doing several search and replaces on substrings is not the same thing as doing the search and repalce in one go, but if you pick sensible substrings it will yield the same result in practice.

Ah hah .. you done it already. Nice way to spend a Sunday afternoon too

Edited by ATG on Sunday 20th May 15:11

[aceparts_com]

I copied the cells from Excell, pasted them in to notepad and saved them. Then opened the file using Word, find/replaced then saved as .txt before importing in to Excell as Tab delimited, copied and pasted the cells back to where they came from.

See, I don't mind faffing about.

[FunkyGibbon]

aceparts_com said:

Thanks :O)

Cracked it!

glad you got it sorted.

cheers

FG