Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How Do I Replace Paragraphs With New Paragraphs In Excel?
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How Do I Replace Paragraphs With New Paragraphs In Excel?
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. "vselin1" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How Do I Replace Paragraphs With New Paragraphs In Excel?
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" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How Do I Replace Paragraphs With New Paragraphs In Excel?
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! "Joel" wrote: 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. "vselin1" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How Do I Replace Paragraphs With New Paragraphs In Excel?
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 "Fred Smith" wrote: 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" wrote in message ... 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How Do I Replace Paragraphs With New Paragraphs In Excel?
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" wrote: 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! "Joel" wrote: 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. "vselin1" wrote: 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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How Do I Replace Paragraphs With New Paragraphs In Excel?
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" wrote: 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" wrote: 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! "Joel" wrote: 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. "vselin1" wrote: 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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How Do I Replace Paragraphs With New Paragraphs In Excel?
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. "vselin1" wrote: 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" wrote: 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" wrote: 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! "Joel" wrote: 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. "vselin1" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to format large word paragraphs into an excel file | Excel Worksheet Functions | |||
Formating excel multiple paragraphs and colums using one source of data | Charts and Charting in Excel | |||
Conditional Paragraphs | New Users to Excel | |||
How can I enter multiple paragraphs in a single Excel cell? | Excel Discussion (Misc queries) | |||
Moving paragraphs from MS Word to Excel | Excel Discussion (Misc queries) |