Excel fields too large to work with, any way around this?

Hi,

I believe once the Excel cells/fields get too big (individual cel
content size) some of the format
and find/search/delete functions, macros, and utilities seize to wor
become it is somehow truncated and 
the entire cell doesn't even get processed. How do I go around thi
inherent limitation? 

I'm currently using ASAP Utilities for Excel. What I use most
often is the "Advanced Character Removal" Tool under the Text Menu of
ASAP. It works great, However this Advanced Character Removal too
completely FAILS once the individual field/cell sizes become to
large.Some of the cells in my Excel sheet have a LOT of Data (mostl
memos,words,email transcript(s),etc) within each of the indivuda
cells. 

I have a bunch of 010 and 013 empty box characters and use ASAP to hel
me get rid of them, however ASAP fails to erase anything from cells tha
contain too much data. (Note: I have used VB MACROS to accomplish th
same thing, and again here once the cells get too large the same thin
happens, which makes me thing this is some kind of limitation/bug in M
Excel itself!)

IS there anyway around this???? 

Here's a link to my previos post for clarification:
http://www.excelforum.com/showthread.php?t=523214

Lots of ideas from people have helped a lot, but now I'm stuck with 
having too large fields, and I can't compromise the size, so what t
do?
Both the macros and the ASAP utilities fail to response to th
individual contents inside the cells once
each cell contains too much data!? Why should this happen? Is there 
way to remove the Excel limit??Thanks,
Bo


Excel fields too large to work with, any way around this

--
bxc273
-----------------------------------------------------------------------
bxc2739's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3253
View this thread: http://www.excelforum.com/showthread.php?threadid=52355

0
3/17/2006 2:19:08 PM
excel 39879 articles. 2 followers. Follow

2 Replies
413 Views

Similar Articles

[PageSpeed] 33

Bo,
I would like to try my XL Companion Excel add-in on the data.
Could you send me a sample sheet containing problem and non problem cells.
Remove XXX from my email address... jim.coneXXX@rcn.comXXX

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"bxc2739" wrote in message...
Hi,
I believe once the Excel cells/fields get too big (individual cell
content size) some of the format
and find/search/delete functions, macros, and utilities seize to work
become it is somehow truncated and 
the entire cell doesn't even get processed. How do I go around this
inherent limitation? 

I'm currently using ASAP Utilities for Excel. What I use most
often is the "Advanced Character Removal" Tool under the Text Menu of
ASAP. It works great, However this Advanced Character Removal tool
completely FAILS once the individual field/cell sizes become too
large.Some of the cells in my Excel sheet have a LOT of Data (mostly
memos,words,email transcript(s),etc) within each of the indivudal
cells. 

I have a bunch of 010 and 013 empty box characters and use ASAP to help
me get rid of them, however ASAP fails to erase anything from cells that
contain too much data. (Note: I have used VB MACROS to accomplish the
same thing, and again here once the cells get too large the same thing
happens, which makes me thing this is some kind of limitation/bug in MS
Excel itself!)
IS there anyway around this???? 
Here's a link to my previos post for clarification:
http://www.excelforum.com/showthread.php?t=523214

Lots of ideas from people have helped a lot, but now I'm stuck with 
having too large fields, and I can't compromise the size, so what to
do?
Both the macros and the ASAP utilities fail to response to the
individual contents inside the cells once
each cell contains too much data!? Why should this happen? Is there a
way to remove the Excel limit??Thanks,
Bo

Excel fields too large to work with, any way around this?

0
jim.coneXXX (771)
3/18/2006 4:33:57 PM
bxc2739 wrote...
>I believe once the Excel cells/fields get too big (individual cell
>content size) some of the format
>and find/search/delete functions, macros, and utilities seize to work
>become it is somehow truncated and
>the entire cell doesn't even get processed. How do I go around this
>inherent limitation?
....

For some things, 255 chars are the most Excel can handle; for other
things, the limit seems to be 1,024 chars.

>I have a bunch of 010 and 013 empty box characters and use ASAP to help
>me get rid of them, however ASAP fails to erase anything from cells that
>contain too much data. (Note: I have used VB MACROS to accomplish the
>same thing, and again here once the cells get too large the same thing
>happens, which makes me thing this is some kind of limitation/bug in MS
>Excel itself!)
....

FWIW, I start with the formula

=REPT("|"&REPT("----+----0",25)&"#",80)

which gives a string of length 20,160 chars. I copy the cell containing
this formula and paste special as values into some other cells. Excel's
own Edit > Replace to replace all # chars with nothing fails, but the
following macro works (in XL9 (2000) and later).

Sub foo()
    Dim c As Range, t As String
    For Each c In Selection
        t = c.Value
        t = Replace(t, "#", "")
        c.Value = t
    Next c
End Sub

Edit > Replace likely fails because it works with each selected cell's
..Formula property. If your macros are also using the .Formula property,
that's why they fail. Work with each cell's .Value property instead.

0
hrlngrv (1990)
3/19/2006 1:02:40 AM
Reply:

Similar Artilces:

number rounding in MS Excel
Hi, This may have been answered many times. In MS EXCEL is there a way that it calculates all numbers to 2 decimal digit accuracy instead of the 15 digit default? Thanks in advance for the answer. Hi You can set your decimal places in your cells as 2 and then check Precision As Displayed on the Tools / Options / Calculation page. Be careful though, it means what it says!! -- Andy. "mahusain" <abidh@bdnet.net> wrote in message news:ab59c6f6.0404200315.5196e8aa@posting.google.com... > Hi, > > This may have been answered many times. > > In MS EXCEL is ther...

forward to: field
Hi Everyone, im looking for the AD attribute in which the 'forward to:' field of the delivery options is stored. Thank you in advance kind regards marc -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ Its in the altRecipient attribute. -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Marc Wenger" <haga(at).iesg--nspm-noa9h.gmx.ch> wrote in message news:opr64hs7z6fyi4rt@news.microsoft.com... > Hi Everyone, > > im looking for the AD attribute in which the 'forward to:' field of ...

SpreadSheetML (XML for Excel)
Hello All, A client of mine has Office X for Mac. I am wondering if this edition of Excel supports SpreadSheetML. I have heard that it also support the new Open XML formats (same as Office 11). Does anyone know if either of these statements is accurate? Thanks in advance, -KJ Hi KJ: I believe that both statements are wrong. The XML converter for Office Next on the Mac has not yet been developed. It's not likely to come until after the PC Office team ships their product to retail -- until they stop CHANGING the thing :-) My guess is that the converter won't appear until sometime...

Public Folder link to Excel File
Hi - I have a user that claims that he had a shortcut to a spreadsheet "in his public folders" at his old job. Obviously, he's an end user that may or may not know what he's talking about.... I'm trying to figure out the best way to accomodate him. He basically wants an Excel file stored on a shared folder to be available to all user as either a shortcut, or a link in the public folders (can't post to a folder as he will be updating the sheet regularly). When a user clicks on the shortcut/link, it would just launch Excel and display the file. Anybody have any ideas...

Filters in Excel 2003
I am using Filters in Excel 2003. Every cell in sheet B is linked to every cell in sheet A. That way I can alter data in B without corrupting the original data in A. I then highlight the cells in B that I want to assign a filter to and select Advanced Filter. I have no criteria so I do not set that. I click OK. I then select Filter again and this time select Auto Filter. Drop down arrows appear at the top of my columns. Great, no problems so far, I then select from the drop down list the criteria that I want to filter. Again no problems, my list filters correctly. But once I have do...

Excel Opens Without Displaying Workbook
I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Displaying Workbook (http://support.microsoft.com/default.aspx?scid=kb;en-us;158996&Product=xlw97), but neither of the resolutions fixed the problem. Any suggestions?? Are you using Excel 97? -John Baughman Fort Collins, CO >-----Original Message----- >I am having issues with opening an Excel file. The file opens, but the workbook is not displayed. I tried the resolution in the article XL97: Excel Opens Without Di...

custom field for projects with tasks past due
I am wondering how/what formula would be needed for creating a custom field at the project level to show when a project has at least one task that is past due. I have a custom field at the task level (IIf([Finish]<[Current Date] And [% Complete]<100,1,2)) that will show when an individual task is overdue, but I want to add a custom field to the Project Center > Summary view to show when a project has any overdue tasks. The PM can then drill down from there into the project and see exactly what tasks are overdue. Once I figure out the formula for this I want to assign ...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Getting the BCC field by default.
I would like the BCC field to show by default but cannot find the setting for this in 2007. Anyone know where this is located? In a new message window, use the Options chunk to display the "Show BCC." --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, JC HARRIS asked: | I would like the BCC field to show by default but cannot find the | setting for this in 2007. Anyone know where this is located? Thanks Milly. I had...

Non-VBA way of making custom menus.
Sorry for the new thread but even on Google, the thread isn't showing up. Well, it was SOOO easy, as I knew it would be. The webpage I quoted in my message this morning didn't mention the "New Menu" at all! So, here is the non-VBA way to create a custom menu: - TOOLS > CUSTOMIZE - under the categories available choose NEW MENU and then drag the NEW MENU option under the Commands window up to the menu bar (like D'UH!! <lol> Wish all those hours spent searching yielded webpages that gave this! <g>) - THEN one can go back to the MACROS category a few line...

View original Excel after saving over it
In error, i hit save instead of save as. I'd like to view the original documents contents. is this at all possible. i use windows xp. excel 2003 Not very likely. Try a Google search action if you haven't already, but I think it's impossible. This underscores the importance of backups. -- Wigi http://www.wimgielis.be = Excel/VBA, soccer and music "houb" wrote: > In error, i hit save instead of save as. I'd like to view the original > documents contents. is this at all possible. i use windows xp. excel 2003 In article <C2A3F0E7-24E0-43A5-809A-ECA719...

printing imported gif files in Excel
When printing gif file with transparent background on the grey(25%) background of Excel non-transparent part of the picture has white border around it. Is there any way to prevent it? Thank you in advance... Tim ...

ExCel programming
I am working on a spreadsheet for work where it will calculate commissions to be earned based on my sales. There is a table that I have to use to determine what the percentage would be, how would I write an equation for that. For example, the equation would have to say: if say D3 was = or < than %, use cell# whatever and then have a sum in there. The table is on another sheet in the document so that would have to be there too. Basically, the way that my commissions are calculated is that we have to take what I sold in any given month and find out what percentage of my yearly plan i...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Ctrl+D not working on outlook messages
Any idea why? alltimefav wrote: > Any idea why? And what are you expecting to happen? "alltimefav" wrote in message news:a95c52df-a387-4a8f-bf9d-269ff485451f@i29g2000prf.googlegroups.com... > Any idea why? So instead of hitting Ctrl+D to delete an item, what happens when the item is selected and you hit the Del key, or the "X" toolbar button, or right-click on the item and select Delete? Did you really expect a detailed response for such a vague question? You didn't even bother to say what "not working" means, like the item does not get delete...

how to make macro work even a sheet is hidden.
hello there I have macro assigned to a button in Sheet 1 which goes to sheet 2. Press a button to Refresh and then PRINT preview It does work in normal state. I do not want the user to see the Sheet 2. I hid sheets from Format-sheet-hide. but the macro is not working when sheet 2 is hidden. how to overcome this error "can't execute code in break mode" the macro code is Sub Print_Preview() Sheets("PaySlips2009-10").Select Application.Run "'Latest 2009Payslip.xls'!Sheet2.HURows" ActiveWindow.SelectedSheets.PrintPreview ...

How to save Japanese characters in CSV format of Excel ?
Hi, I would like to know, how to save the Doubly-Byte characters.For example, Japanese in CSV of Excel.Here are the steps, I tried. 1. Open Excel 2. Copy pasted the Japanese charactesrs in a cell.It is displaying the characters correctly. 3. Save as CSV(Comma seperated file) It is saving as ??????. Thanks in advance for the info. ...

COUNTIF on Summary Fields
Can't use on a cell that "sums" with arithmetic operators "+". Is there a workaround, etc.? a bit more explanation? -- Don Guillett SalesAid Software donaldb@281.com "DLC" <dlcopesr@yahoo.com> wrote in message news:117v954kaf4s2f6@corp.supernews.com... > Can't use on a cell that "sums" with arithmetic operators "+". Is there a > workaround, etc.? > > ...

Macro
I need a macro that help me to transfer name and address information from an specific table in excel to a template in words on specific areas and then print the word document. The reason for this is that i need to create diferents letters to be sent to the customers from the excel table. Example of the table is: soc seg, customer name, child name, customer code, add 1 , add2, city, estate, zip code. all this information will be paste on word letter template on specific areas or fields. Any suggestion!!! -- nicoro Hi IMHO the best approach would be to set up a mail merge documen...

why does my spell check not work?
My spell check has never worked. What can I do to set it up? In what program / version "karen" <karen@discussions.microsoft.com> wrote in message news:DA8883BD-0958-4AB0-93FF-F43E4FB9D176@microsoft.com... > My spell check has never worked. What can I do to set it up? ...

Excel fun patch
Excel fun patch, have you see it? http://www.conus.info ...

Excel 2003
Sometimes when I receive excel attachments in my email (outlook 2003) they have a row height of 409.5 when I open them. I have to highlight the whole sheet and change the row height before I am able to view the data. This does not happen all the time and not from any particular person. I can forward the email to another machine with excel 2003 and they open it with no problem. I have downloaded all the patches and updates that I can find and still no help. This is just an annoying quirk that is driving me nuts. Does anyone have any ideas? TIA How about a couple of silly guesses that might t...

Help about numeric type field. Thanks.
I created a SQL Server 2005 CE DB ( .sdf, version 3.0) with vs2005. And I created a table which has 2 fields: fld1 - int, fld2 - numeric(38,25). But I encounted an error messagebox when I tried to insert a record (4005,9000000). The msgbox said Conversion overflows. The setting for my numeric fld2 is (precision=38,scale=25). So why occur error when to insert 9000000? Thanks in advance. ...

Need macro help to close excel
I have created a button in Access2000 that opens an Excel Spreadsheet. What I need now is assit in closing excel upon completion. I can get an excel macro to save my file and close the worksheet, but it is not closing excel entirely. I'm on project with this employer and could use a response today to fix this before I leave. Thanks much to any and all. My macro is as follows: Sub SaveClose() ' ' SaveClose Macro ' Macro recorded 9/27/2004 by cdjohnso ' ' Keyboard Shortcut: Ctrl+Shift+C ' ChDir "I:\SchoolsSurvey\Graphs_Reports" ActiveWorkb...

Cell with large amount of data not showing all data
I'm running Excel 97. I have a cell with 358 words (1928 characters with spaces). Word wrap is on for the cell. Only part of the text is displayed even though the cell is big enough to show everything. If I make the cell wider (wider than a page) more of the text shows but not everything. I tried a new worksheet with the same text and had the same problem. Is this a known issue with excel? Is there a solution? Thanks, Brad Left to its own devices, excel will only show about 1000 characters in a cell. But you can add some alt-enters (to force a new line within the cell) and see more s...