How Do I Replace Paragraphs With New Paragraphs In Excel?

[vselin1]

Hi -
We downloaded an excel spreadsheet with about 4000 rows of different text
and about 10 different colums of text. One of the colums contains either
short amounts of text or large amounts of text that include basic html such
as <br>, <li>, etc.

Instead of using the "find and replace" tool...which allows you to change
about one sentence at a time for another one.....is there a way to change
whole paragraphs? "Find and replace" is great....but the amount of text you
can place in it is limited. We would like a tool that allows us to replace 5
or 6 sentences at a time.
Any thoughts on how to do this....or any 3rd party tools that would do this?

Also...many times we get the "formula is too long" error message....we don't
know what this means and when we get it, we are not able to change the text
in that cell with the "find and replace"....if anything.....how do we advoid
getting this message....if we had to i guess we could change a sentence at a
time with the "find and replace".....but it's not allowing us to get past
that point.

thanks everyone!

 

[Shane Devenshire]

First, the Find and Replace command allows up to 255 characters (this is true
for 2003 and 2007). I don't know if anyone makes an add-in to extend this
limit. You could write VBA code to do it.

Second, the formula is too long message occurs when a formula (which can be
a maximum of 1024 characters in 2003 or 8192 in 2007 is exceeded. This might
happen if you tried to do a find and replace on a range that include
formulas, For example, if you tried to replace all A's with
"abcdefghijklmnopqrstuvwxyz" and you had a formula which read something like
=IF(B1="AAA","Across an airy plain autumn falls unaided","")

 

[Joel]

A cell can contain up to 32,000 character, only the 1st 1024 gets displayed.
formulas are limited to 256 characters. If excel thinks there is a formula
(which starts with an = sign) then it will give an error if the string
contains more than 256 characters.

I macro can replace all the characters. What characters are you replacing
and which column are you using?

Usually it is better to make the substitions before thte workbook get loaded
into excel becasue excel often make changes you don't want. One option is to
use a text editor like Notepad to make the substitions before you import the
data into excel.

 

[Fred Smith]

Given that you have Excel, the best third party tool would be Microsoft
Word. Given that this is a completely text based operation, why use Excel at
all?

Regards,
Fred

 

[vselin1]

joel -
thanks for the help......i copied and pasted the cells into notepad. it
allows me to add as much text as i want in the find and replace tool...but
when i try to use it...it tells me that it cannot find the text i am trying
to replace....it does this each time i try...any other suggestions? thanks!

 

[vselin1]

fred -
thank you!
the reason i need to use excel is that it's a program within yahoo & that is
the only way to download all this info...as a csv or excel...thoughts?
thanks

 

[Joel]

Notepad will remove any unicode characters from the file. That is why you
can't replace something that was removed by notepad. I thought the
characters you were trying to replace where regular characters (not unicode).

You don't need to text data into excel to convert to CSV. I have converted
files into CSV using VBA and not using the spreadsheet. I open two text
files from VBA and read one, convert the data, and then output the results
directly into the 2nd file.

I you post a sample of the text file I will work on it later today.

 

[vselin1]

thanks again joel!
so i copied and pasted the column out of excel and placed it in notepad. i
am not familiar with unicode, even when i looked it up i still did not
understand it
but when i did use the find and replace tool...all i did was copy the text
directly from the notepad copy....it was not being copied from another format.
when i save the notepad, it asks to save it as ANSI....should i do this
first, then open it up again and try to edit it that way?
the text that i am using in the cell is just regular text...as far as i
know....mixed in with some basic html like <br><b><li>....if i edit it
through the notepad way you suggested, am i going to lose any of that info?
i guess basically is anything going to change if it's not saved in the same
format it came out as?
thanks again!!

 

[Joel]

html is hyper-text meta language. The items in angle brackets are called
tags. the tags items in your case is probably formating information that
isn't used by CSV.

Text is 8 bit data and unicode is 16 bit data.

CSV is 8 bit data and is strictly text with no formating. The tagged data
isn't important. One thing you may try is reading the file with microsoft
word and then have word save the file as text only. This will remove the
tags. Notepad will keep the tags and remove and special characters
(including unicode).

You may need to read file in word, and then notepad to get rid of all the
problems.