replace function truncates text

Hi y'all:

I am sorry if this is a repost I can seem to find the original message.


I am haveing a peculiar problem with the replace function of excel 200 and
2002.
I am replacing a token (@@@@@@) with a vblf within a range of cells.
The macro works fine when in most cases. But when the cells contains large
blocks of text,  around 900 characters, anything above that disappears.

Any clue
Thanks
Marcelo Rizzo


0
mrizzo (3)
8/27/2003 7:41:45 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
370 Views

Similar Articles

[PageSpeed] 45

Marcelo,

I had a similiar problem with the SUBSTITUTE function after character 911 in
a long text cell.
Which function are you using?

It must be some deficiency in Excel.

I'll be interested to see anyone else posting on this topic with more info.

regards,

JohnI

"Marcelo Rizzo" <mrizzo@medialocate-usa.com> wrote in message
news:O#fXANNbDHA.1488@TK2MSFTNGP12.phx.gbl...
> Hi y'all:
>
> I am sorry if this is a repost I can seem to find the original message.
>
>
> I am haveing a peculiar problem with the replace function of excel 200 and
> 2002.
> I am replacing a token (@@@@@@) with a vblf within a range of cells.
> The macro works fine when in most cases. But when the cells contains large
> blocks of text,  around 900 characters, anything above that disappears.
>
> Any clue
> Thanks
> Marcelo Rizzo
>
>


0
8/27/2003 9:25:19 PM
That sound about right.
I wonder if the 911 is an ms programmer's idea of a joke. Emergency
Help,Help!!

I am using the REPLACE function.
I also tried to using vba. But when I select the text (str$ = range.select)
not all of the text is extracted. And to add insult to injury, when the text
is placed back in the cell  (range.formulaR1C1) even more text is cropped.
So, I think you are right is definately a deficiency.


Cheers,
Marcelo
"JohnI" <john.iseppi@iinet.spamfooler.au> wrote in message
news:%23JCG$GObDHA.2476@tk2msftngp13.phx.gbl...
> Marcelo,
>
> I had a similiar problem with the SUBSTITUTE function after character 911
in
> a long text cell.
> Which function are you using?
>
> It must be some deficiency in Excel.
>
> I'll be interested to see anyone else posting on this topic with more
info.
>
> regards,
>
> JohnI
>
> "Marcelo Rizzo" <mrizzo@medialocate-usa.com> wrote in message
> news:O#fXANNbDHA.1488@TK2MSFTNGP12.phx.gbl...
> > Hi y'all:
> >
> > I am sorry if this is a repost I can seem to find the original message.
> >
> >
> > I am haveing a peculiar problem with the replace function of excel 200
and
> > 2002.
> > I am replacing a token (@@@@@@) with a vblf within a range of cells.
> > The macro works fine when in most cases. But when the cells contains
large
> > blocks of text,  around 900 characters, anything above that disappears.
> >
> > Any clue
> > Thanks
> > Marcelo Rizzo
> >
> >
>
>


0
mrizzo (3)
8/27/2003 9:41:14 PM
Maybe you could try this:

'for xl2k and above
Option Explicit
Sub testme01()
    Dim myCell As Range
    For Each myCell In Selection.Cells
        myCell.Value = replace(myCell.Value, "$$$$", Chr(10))
    Next
End Sub

'for xl97
Option Explicit
Sub testme02()
    Dim myCell As Range
    For Each myCell In Selection.Cells
        myCell.Value = Application.Substitute(myCell.Value, "$$$$", Chr(10))
    Next
End Sub

I put some test data into a cell.  About 1200 characters.  Each worked ok, and
I'm using xl2002.

Marcelo Rizzo wrote:
> 
> Hi y'all:
> 
> I am sorry if this is a repost I can seem to find the original message.
> 
> I am haveing a peculiar problem with the replace function of excel 200 and
> 2002.
> I am replacing a token (@@@@@@) with a vblf within a range of cells.
> The macro works fine when in most cases. But when the cells contains large
> blocks of text,  around 900 characters, anything above that disappears.
> 
> Any clue
> Thanks
> Marcelo Rizzo

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
8/28/2003 1:39:22 AM
Thank You that worked like a charm


Marcelo




"Dave Peterson" <ec35720@msn.com> wrote in message
news:3F4D5D4A.425D9BD8@msn.com...
> Maybe you could try this:
>
> 'for xl2k and above
> Option Explicit
> Sub testme01()
>     Dim myCell As Range
>     For Each myCell In Selection.Cells
>         myCell.Value = replace(myCell.Value, "$$$$", Chr(10))
>     Next
> End Sub
>
> 'for xl97
> Option Explicit
> Sub testme02()
>     Dim myCell As Range
>     For Each myCell In Selection.Cells
>         myCell.Value = Application.Substitute(myCell.Value, "$$$$",
Chr(10))
>     Next
> End Sub
>
> I put some test data into a cell.  About 1200 characters.  Each worked ok,
and
> I'm using xl2002.
>
> Marcelo Rizzo wrote:
> >
> > Hi y'all:
> >
> > I am sorry if this is a repost I can seem to find the original message.
> >
> > I am haveing a peculiar problem with the replace function of excel 200
and
> > 2002.
> > I am replacing a token (@@@@@@) with a vblf within a range of cells.
> > The macro works fine when in most cases. But when the cells contains
large
> > blocks of text,  around 900 characters, anything above that disappears.
> >
> > Any clue
> > Thanks
> > Marcelo Rizzo
>
> -- 
>
> Dave Peterson
> ec35720@msn.com


0
mrizzo (3)
8/28/2003 2:30:34 AM
Reply:

Similar Artilces:

find and replace hyperlink?
Hi, I want to globally find and replace the contents of a hyperlink with a new hyperlink. For example, I want this Jul.xls#'Title Page'!A1 to be replaced with Aug.xls#'Title Page'!A1 I can't seem to find a way do it. Is there a way? Thank you, Mike Take a look at David McRitchie's site: http://www.mvps.org/dmcritchie/excel/buildtoc.htm look for: Fix Hyperlinks (#FixHyperlinks) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm me wrote: > > Hi, I want to g...

Histogram Function has a problem
When I create a histogram in the 2007 Beta, I get not just my data graphed, but also the bin range along with it. How do I get EXCEL to stop doing this automatically? Use 2003? You shouldn't expect something called a "Beta" to be ready for real work. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Boopsie" <Boopsie@discussions.microsoft.com> wrote in message news:662F0691-7188-4DAA-8BE5-8E75C2C3ECD6@microsoft.com... > When I create a histogram in the 2007 Beta, I get not just my data >...

Offset text print problems
recently all of my excel documents are printing the text offset from the gridlines. I have checked through every setting I can think of and am having no luck. I have even changed the font I am using. Any ideas on why this is happening and or how to fix it ? I have no idea, but I'd start with the printer driver. Either get a fresh or new driver from your printer's manufacturer. You may even want to try it on a different printer to see if that makes a difference. IMC Techie wrote: > > recently all of my excel documents are printing the text offset from the > gridlines....

Center text in text box
I produced our club's latest newsletter on Publisher for the first time and overall was a pretty pain free experience. Very intuitive program. I was unable to find a command to center text within a text box. Is this feature not present in Publisher as it is in word? It was not on the tool bar with the margin justifying commands and my quick search didn't bring it up. I ended up ball parking my lines in the center with tab and space bar. Any suggestions? Right-click the toolbar, tick "formatting". -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/...

Replace word in sentence function...
I have the following function which replaces a word in a sentence with "....." =REPLACE(I2,SEARCH(E2,I2),LEN(E2),".....") Where the word is E2 and the sentence is I2. At the moment, if the word is "work" and the sentence is "I worked at home" I get the sentence "I .....ed at home". Any ideas how I could capture any remaining letters at the end of the word and get the result "I ..... at home"? I'd greatly appreciate your help. Thanks in advance, Chris can you upload example, would be easier to view -- Message posted from h...

Hyperlink function presents variable results does not work in macro
I need some assistance if possible using a hyperlink function within a macro The hyperlink function is presenting the current address in excel (address varies) but there is no change within the macro code It appears that the macro once recorded cannot be "updated" to the address that is current. Is there a way around, through or over this. I am using the hyperlink to select a range which can then be "updated" as part of the macro Thanks for any assistance -- Message posted via http://www.officekb.com As ALWAYS, post your coding efforts for comments. --...

Function/Formatting
Have a spreadsheet with a range of numbers(for ref the last 30 lotter draws) in cells A1:g31. What I would like is - if a number is entere in say cell m2, a formula or conditional format that would highligh that number in the range. Is it possible? Thanking everyone in advance. Scap ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com One way, select A1:G31 with A1 as the active cell (if you start selecting from A1 it will be active) or select it by typing A1:A31 in the n...

Replacing Powerpoint 97 SR-2 on M/S Office 97 Pro Edit on Win XP
I dread asking this in case someone descends on me like a ton of bricks and says the answer is in the posts already! - but i I have never used the PP 97 which came with the MS Office 97 Pro Edit I have installed on my computer which uses Win XP with Ser Pack 3. I am about to embark on using PowerPoint for the FIRST time and wonder whether I should, before anything else, acquire a more up to date version first - and one that will work problem free with my XP - which version should I go for and will it, on loading, overwrite and replace the Powpoint I already have and still operat...

Search and replace problem
Hi, I have an Excel worksheets where in several cell's a symbol was added to mark the cell's "to review". The symbol used was "(*)". I have tried now to delete all the entries with a "search and replace", but Excel interpretes the asterisk as a DOS-symbol and deletes everythng between brackets. Is there a way to avoid that or to let Excel know that I only want to delete the specific characters. TIA Melissa It's because it is a wildcard, you can replace it by preceding it with a tilde ~ which tell Excel to find an asterisk in find what p...

DATEDIF Function Should Be More Accesible
The DATEDIF function should be accessible through the Paste Function button. It isn't listed in the Date &Time list or even under ALL. It's a very usefull function, why would they hide it? There is help, but you have to learn it exists from another source first. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader...

Access 2002/2003 VBA What kind of file is .CRC (Binary, text) ? How do I read it with FreeFile()?
Dear All Access 2002 w/SP3 Access 2003 w/SP2 Windows XP Pro w/SP2 Type of file: CRC File Opens with: Unknown application I want to read all the data in a .CRC file I know I can read it using FSO, if I click on File name -> right click on properties, Changes and choose the notepad to open it. But I do not want to do this, I want to open the file as is using FreeFile() and read all the data The file was created using a program called CdCheck http://www.kvipu.com/CDCheck/ Can someone please help? Thanks in advance Regards, Antonio Macias rebelscum0000 <piiixp@jedikeeper.com> wro...

Copy cell contents, then paste into the same cell with other text.
Hi! I tried a search first and couldn't find anything like this. My spreadsheet has a column for shipping that takes a series like this for each product: ?0.0*0.13.2*d*0x0x0:07:24:04 Following the question mark is the handling charge (0.0 in this example). This is followed by an * and then the weight of the item (0.13.2 in this example which is 13.2 ounces) I have a list of product weights in a colum with just pounds and ounces. I need to copy that information, then paste it into the weight area of the string above and then paste those modified contents back into t...

replace a negative result with a zero?
I have my formula entered but need it to return a zero if the result is negative. Any help is greatly appreciated. -- Pam Well, what is the formula that you are using? You could try this: =3DMAX(your_formula,0) to replace your_formula. Hope this helps. Pete On Apr 19, 5:17=A0pm, Pam2277 <Pam2...@discussions.microsoft.com> wrote: > I have my formula entered but need it to return a zero if the result is > negative. =A0Any help is greatly appreciated. > -- > Pam Hi, =if(your formula<0,0,your formula) "Pam2277" wrote: > ...

Text Overlayment
Is there a way to overlay on letter with another - so that both are visible? Or is there some way to create a composite? For examples: M overlaying W or P overlaying X or T overlaying O? WordArt? Create a text box on the Master page with your first letter, on the publication page create another text box with the second letter. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "PatJennings" <pfjennings@charter.net> wrote in message news:usKPm$xfGHA.356@TK2MSFTNGP02.phx.gbl... > Is there a way to overlay on l...

Selective Replace in Search and Replace
I need to search and replace in a large document. I don't want to replace the entire search term/expression just part of it. In this case I am searching for a paragraph return followed by any letter (not digit) and then want to change that to paragraph/tab but keep the letter. The search looks like this ^p^$ but naturally if replace is ^p^t I lose the first letter of the text string which I don't want How can I construct either search and replace so that ^p Alexander turns into ^p ^tAlexander and not ^p ^tlexander Thanks in a...

Is the text implying that FORMSOF(INFLECTIONAL...) will match either nouns or verbs, but not both?
hi 1) > A given <simple_term> within a <generation_term> will not match both noun= s and > verbs. If I understand the above text correctly, then query SELECT * FROM someTable WHERE CONTAINS ( *, ' FORMSOF ( INFLECTIONAL, park ) ' ) will search for either nouns or verbs derived from the root word =93park=94, but not for both? Thus out of the two rows, one containing noun =93parks=94 and other verb =93parking=94, the above query should retur= n just one of the two rows? But as it turns out, query returns both rows, so perhaps my assumptions are a ...

AutoCorrect
In my AutoCorrect window next to Replace and With, why are the options plain text and formatted text greyed out? "Formatted text" is available only if you have selected formatted text before opening the dialog (or pasted in text that has characters that require formatting). "Formatted text" means any text that contains characters from more than one font or that has, say, italic or bold formatting applied. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Elizabeth" <Elizabeth@discus...

Problem with Concatenate Function
Hi I have a problem with the "Concatenate" function in Excel 2002. I can create the function in one individual cell and it works fine, s for example I have the cells: 2 2 so after concatenation it gives me "22". However, when I try and copy that formula (by dragging the cell dow the column) the formula doesn't work properly. So all I get is "22" copied. The formula itself HAS copied into th new cells but just produces "22". Any ideas? Thanks Marti -- Message posted from http://www.ExcelForum.com Hi Make sure that Calculation is s...

What function to select the last 3 smallest integers?
Does it have a single function to perform the similar task? Given Lists {1,2,3,4,5,6,7,8,9} Given number {2,3,7} Condition: If any given number equals to any last 3 small integers, such as {1,2,3} in this case, then TRUE. 2 is TRUE 3 is TRUE 7 is FALSE Does anyone have any idea? Thank you Eric =ISNUMBER(MATCH(3,SMALL(A1:A9,{1,2,3}),0)) where A1:A9 holds the list of numbers -- HTH RP (remove nothere from the email address if mailing direct) "Eric" <Eric@discussions.microsoft.com> wrote in message news:4F9D4C71-4BBB-4585-984E-7B29DFFF85F2@microsoft.com... > Does it ...

How do I check error result occurring by filecopy function in VBA
I writing in VBA for excell. My program calls the filecopy function in a private function. If filecopy fails, the program should analyze it and return with a failure value to the main subrotine that calld the fuction (in a success execution of filecopy, the function returns the workbook object of the new destination file). How can I check the status of the execution? How can I found out if the returnd workbook object has value or it's empty? Thanks Hi I tend to do something like Err.clear 'err is not destroyed when the sub ends On Error Resume Next 'do the co...

alternate quoted text formatting?
Is there any way to change Outlook's behaviour vis-a-vi quoted text in replies. It uses vertical bars to indicate quote level, but these just don't show up (in any consistent way) when viewed in any non-Outlook mail client. I'd much prefer Outlook to use ">" for quoted text or at least some method that is not limited to viewing in Microsoft clients. The only way I can find to do this is to use the view all mail as plain text option, but then if I'm forwarded an e-mail from an Outlook user where there is already quoted text in the vertical bars style, these are jus...

Email truncation help needed
A co-worker is my office receives truncated email text when I or someone else sends her an email. The same email can be sent to me without any truncation occurring. We all have the same version of Outlook 2002 and IE 6.0. Any ideas on what's happening & how to fix it? Thanks ...

Problems with the GetFileSecurity function
Hello, I have the following problem. I need to obtain current user's rights for the speciefied file or directory (read, write, delete, etc.) and the file owner name. I've found out that it can be made by using the GetFileSecurity function. But I'm not familiar with all these ACLs, DACLs, etc. So I will appreciate if anyone can help me with some code example. Thanks. Following articles: http://www.codeproject.com/win32/accessctrl1.asp (The Windows Access Control Model Part N) will help you. -- Vladimir "cozzzy" <cozy_dude@mail.ru> wrote in message news:11390...

Replace wildcards with a space
I have a text field with all values containing an asterisk. The asterisk can be located anywhere within the contents. I am trying to replace the asterisk with a space. I will then use TRIM to remove any spaces at the beginning of the text. I have been unable to get an update query to work. When my criteria is like "~[*}", Access says that 0 rows will be updated. When the criteria is [*], I am asked to enter a parameter value. Any assistance will be greatly appreciated. Thanks - bkh Barbara wrote: >I have a text field with all values containing an aste...

Replace more characters using replace
Hi I have number of characters in a string, that I like to replace with "nothing". I use a code like this: Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, " ", "") a = Replace(a, "&", "") a = Replace(a, "+", "") MsgBox a End Sub As I have about 15 characters, that all should be replaced with nothing, I am looking ofr another way to it. Something like Sub test() Dim a As String a = "Jan+& Per" a = Replace(a, {" ",...