 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2008Is there any way to use longer strings in formulas?
In Excel 2002, the longest string I can use in a formula is (to my mind) ludicrously short. I have a workbook that requires HUNDREDS of English sentences like: ="Just to let y'all know, thanks to all the swell work by our sales staff in 2007, and despite our having to fire half of them for insubordination, and despite our chairman's having run off to a region in Asia which we believe is officially called the Glorious Former Soviet Autonomous People's Provisional Revolutionary Socialist Republic of Trans-Caucasian Equitorial Guinea, our company's annual return on investment last year was really pretty good. In fact, it exceeded all expectations and was: "&ROI. Try entering that in a cell in Edsel 2002. I mean, does Microflot literally believe that Excel--one of the superbest apps ever written, don't get me wrong--is never used for English language processing, and that users exclusively use numbers and short strings only? So, is there any way to create, kludge, fudge, or otherwise do an end run to create a formula that will accept long strings without choking? If more recent versions of Excel have fixed this shortcoming, then retract EVERYTHING I said, and I will rush out and buy it. Thanks much. *** Hello, I apologyze for my english. The length of a formula is limited. But if you enter your string with 'my_string and not with ="my_string" in A1 and then put the formula = A1 & ROI in A2 , it will work. NB: with excel 2003 > In Excel 2002, the longest string I can use in a formula is (to my > mind) ludicrously short. [quoted text clipped - 26 lines] > > *** TEXTBOX  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com > In Excel 2002, the longest string I can use in a formula is (to my > mind) ludicrously short. [quoted text clipped - 26 lines] > > *** You get 1024 characters in a formula--when in R1C1 reference style. Maybe you can put the long string in a different cell and then just concatenate what you want: =x99&roi where x99 held a long string. > In Excel 2002, the longest string I can use in a formula is (to my > mind) ludicrously short. [quoted text clipped - 26 lines] > > *** SignatureDave Peterson Your diatribe against MS is misplaced. Excel is an application designed mainly for numerical work and simple databases. It is not designed for paragraphs. Perhaps you need Access best wishes SignatureBernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email > In Excel 2002, the longest string I can use in a formula is (to my > mind) ludicrously short. [quoted text clipped - 26 lines] > > *** "Bernard Liengme" <blien...@stfx.TRUENORTH.ca> wrote... >Your diatribe against MS is misplaced. Excel is an application >designed mainly for numerical work and simple databases. It is not >designed for paragraphs. Perhaps you need Access ... Reality check: there are dozens if not more postings every in which Excel users ask how to [mis]use Excel for text processing. While it may not be Microsoft's intention that Excel be used for such tasks, reality is different. Mostly it's IT departments to blame for this. Many organizations provide most employees with only Office Standard, i.e., Excel but not Access. That means Excel is usually the only tool those employees have to do any sort of programmatic automation without writing code (VBA, VBScript, JScript or batch files). But just to be argumentative, if Excel weren't meant to handle paragraphs, why does it include Edit > Fill > Justify or Format > Alignment > Wrap Text? What purpose do either serve if not to handle longish text strings? >But just to be argumentative, if Excel weren't meant to handle >paragraphs, why does it include Edit > Fill > Justify or Format > >Alignment > Wrap Text? What purpose do either serve if not to handle >longish text strings? > Not to be argumentative :-) , but I most often use Wrap Text to allow for two or more lines of text in a column heading. There are programs available to handle longish text strings. They're called word processors. Craig Craig Schiller <cschill...@earthlink.net> wrote... ... >There are programs available to handle longish text strings. They're >called word processors. Text processing and word processing aren't the same thing. Try using Word (without VBA) to change a list of names and addresses like Name Street Address City, State Zip into records like Name; Street Address; City, State Zip Frankly, I wouldn't try Word, with or without VBA, to do anything unless compelled to by circumstances. >Craig Schiller <cschill...@earthlink.net> wrote... >... [quoted text clipped - 15 lines] >Name; Street Address; City, State Zip > Craig Schiller <cschill...@earthlink.net> wrote... >Frankly, I wouldn't try Word, with or without VBA, to do anything >unless compelled to by circumstances. ... >>Text processing and word processing aren't the same thing. . . . OK, I'll take that as an admission that you now recognize the difference between text processing and word processing. Whatever you say, Harlan. Excel shouldn't be used for either. >Craig Schiller <cschill...@earthlink.net> wrote... > [quoted text clipped - 13 lines] >difference between text processing and word processing. > Craig Schiller <cschill...@earthlink.net> wrote.... >Whatever you say, Harlan. Excel shouldn't be used for either. Maybe not, but if the only tools most people have are Excel, Word, VBA, WSH and batch files, 99 out of 100 them will sensibly choose to use Excel for text processing because they don't know how to program in VBA, WSH scripts or batch files, and Word itself (no VBA) isn't programmable. When the only tool one knows how to use is a hammer, all problems look like nails. If the alternative is that problems are ignored, treating them as nails isn't necessarily a bad thing. Word has VBA Tyro > Craig Schiller <cschill...@earthlink.net> wrote.... >>Whatever you say, Harlan. Excel shouldn't be used for either. [quoted text clipped - 8 lines] > like nails. If the alternative is that problems are ignored, treating > them as nails isn't necessarily a bad thing. "Tyro" <T...@hotmail.com> wrote... >Word has VBA ... You misunderstand (again). Let me clarify that by '(no VBA)' I meant without needing to use VBA. The *ONLY* way to perform text processing, i.e., well-defined automatable/programmable operations on text strings, as distinct from word processing, which is almost always a manual task involving as much formatting as composition, in Word requires using VBA. If one wishes to avoid VBA, possibly because one doesn't know how to program at all in any procedural language, Excel would be a better choice for text processing than Word. and Word itself (no VBA) isn't programmable. > "Tyro" <T...@hotmail.com> wrote... >>Word has VBA [quoted text clipped - 10 lines] > at all in any procedural language, Excel would be a better choice for > text processing than Word. Use Word not Excel Tyro > In Excel 2002, the longest string I can use in a formula is (to my > mind) ludicrously short. [quoted text clipped - 26 lines] > > *** For a black tie affair in March 2002, I prepared an Excel spread sheet with text that was used as the basis for preparing silent auction catalogs and the description on the items being auctioned.. The maximum number of characters per cell is 255, therefore I used up to three columns of cells for this purpose. Each cell with text was accompanied by an adjoining column of cells into which I placed notations like: =LEN(S18) This provided up to 755 characters in the mail merge. In Excel 2007, the maximum is 32,767 characters. > Use Word not Excel > [quoted text clipped - 30 lines] >> >> *** I have Excel 2007. The OP does not. But Excel is not a word processing program. Tyro > For a black tie affair in March 2002, I prepared an Excel spread sheet > with text that was used as the basis for preparing silent auction catalogs [quoted text clipped - 40 lines] >>> >>> *** Also, 2007 provides: Length of formula contents: 8,192 characters > For a black tie affair in March 2002, I prepared an Excel spread sheet > with text that was used as the basis for preparing silent auction catalogs [quoted text clipped - 40 lines] >>> >>> *** The OP has Excel 2002. In any event, Excel is not a word processing program. Word is. > Also, 2007 provides: Length of formula contents: 8,192 characters >> For a black tie affair in March 2002, I prepared an Excel spread sheet [quoted text clipped - 41 lines] >>>> >>>> *** I have written an Excel addin for dealing with cells containing long text strings. It contains a simple cell viewer/editor to allow you to read and edit the content of a cell. You can download it from http://wwwusers.brookes.ac.uk/p0054663/PDGExcelUtils.xla Once it is installed just use the Escape key to start the cell text edit form. Peter Grebenik That's fine. But if you want to work with text, don't use a spreadsheet program like Excel. Use a word processing program like Word. If you want to work with Database, use a database processing program such as Access or FoxPro. Use the appropriate software. In other words, don't try to make a hammer perform as a screwdriver Tyro >I have written an Excel addin for dealing with cells containing long > text strings. It contains a simple cell viewer/editor to allow you to [quoted text clipped - 7 lines] > > Peter Grebenik "Tyro" <T...@hotmail.com> wrote... > . . . But if you want to work with text, don't use a spreadsheet >program like Excel. Use a word processing program like Word. . . . You fail to understand the distinction between text processing and word processing. Simple example: translating terms between different languages. That's possible to do (perhaps not perfectly) in Excel using tables and lookup functions. How would you manage the same thing in Word without resorting to tables? I would never use either for that. I would simply write some code in say C++ use that. Tyro > "Tyro" <T...@hotmail.com> wrote... >> . . . But if you want to work with text, don't use a spreadsheet [quoted text clipped - 5 lines] > using tables and lookup functions. How would you manage the same thing > in Word without resorting to tables? "Tyro" <T...@hotmail.com> wrote... >I would never use either for that. I would simply write some code in >say C++ use that. ... You persist in failing to understand. Most business PC users aren't given C++ compilers, but they usually have signed IT policies that provide for their possible dismissal if they install nonapproved software on their own, and wouldn't know how to write C++ code even if they had a compiler. But they may still need to perform text processing (AS DISTINCT FROM WORD PROCESSING) tasks, and some of those tasks may be fairly easily automated. However, the ONLY tools they're likely to have that they could use to automate such tasks (i.e., the only automatable/programmable software that their IT departments installed on their PCs) are the Office applications, so also VBA as part of Office, VBScript and JScript as part of WSH, which has been part of standard Windows installs since Windows 2000/Windows ME, and batch files. That's all. They're also likely to have NO PROGRAMMING EXPERIENCE with VBA, VBScript, JScript or batch files, but many of them may know a bit about using Excel formulas. In their cases, choosing to attempt text processing in Excel is the MOST RATIONAL alternative. [As for me, I wouldn't screw around with C++ for translation tables. I'd use awk or any other scripting language.] I was not given a C++ compiler. I own one. I've been programming for almost 45 years. Tyro > "Tyro" <T...@hotmail.com> wrote... >>I would never use either for that. I would simply write some code in [quoted text clipped - 24 lines] > [As for me, I wouldn't screw around with C++ for translation tables. > I'd use awk or any other scripting language.] "Tyro" <T...@hotmail.com> wrote... >I was not given a C++ compiler. I own one. I've been programming for >almost 45 years. ... And if you're hired as a programmer, you may have much more latitude than most business PC users. And your level of experience doesn't imply anything about most other people's. *YOU* can use whatever tool you believe is most appropriate on *YOUR* system. Others may not have as many options. xl97 allowed 32k characters per cell. Unless you did something special with alt-enters, you could only see about 1000 of them, though. xl95 had a limit of 255 characters per cell (IIRC). > For a black tie affair in March 2002, I prepared an Excel spread sheet with > text that was used as the basis for preparing silent auction catalogs and [quoted text clipped - 40 lines] > >> > >> *** SignatureDave Peterson "Tyro" <T...@hotmail.com> wrote... >Use Word not Excel ... A clear sign you don't understand the OP's question. bao...@my-deja.com wrote... >In Excel 2002, the longest string I can use in a formula is (to my >mind) ludicrously short. [quoted text clipped - 11 lines] > >Try entering that in a cell in Edsel 2002. ... Since the bulk of this is constant text, enter the constant string AS CONSTANT TEXT in another cell, say X99. X99: Just to let y'all know, thanks to all the swell work by our sales staff in 2007, and despite our having to fire half of them for insubordination, and despite our chairman's having run off to a region in Asia which we believe is officially called the Glorious Former Soviet Autonomous People's Provisional Revolutionary Socialist Republic of Trans-Caucasian Equitorial Guinea, our company's annual return on investment last year was really pretty good. In fact, it exceeded all expectations and was: Include newlines in it by pressing [Alt]+[Enter] to form multiple lines of about the same length and include a trailing space at the end. Then use a formula like Y100: =X99&TEXT(ROI,"0.00%") and set that cell to wrap text to produce the result you seem to want. Hi, 1. The Maximum length of a formula in 2003 and earlier is 1,024 in 2007 it is 8,192. 2. The maximum length of a text entry is 32,767 characters in both versions. 3. Only the first 1,023 text characters can be viewed on the screen in 2003 and earlier in 2007 that is 32,768. 4. In 2003 and earlier you can put more characters in a comment or a text box and see them! But that doesn't help for formulas. 5. Although you CAN concatenate two cell with 1024 characters into one cell, you can't see the results because of item #3 above. Cheers, Shane Devenshire > In Excel 2002, the longest string I can use in a formula is (to my > mind) ludicrously short. [quoted text clipped - 26 lines] > > *** Best to use a Word processor. Let Excel handle numbers to its limited ability and let word processors handle text and words. Tyro > Hi, > [quoted text clipped - 43 lines] >> >> *** #3 and #5. You can see lots more than 1024 characters in the cell if you add alt-enters (char(10)'s) every 80-100 characters. > Hi, > [quoted text clipped - 42 lines] > > > > *** SignatureDave Peterson "Shane Devenshire" <shanedevensh...@sbcglobal.net> wrote... ... >3. Only the first 1,023 text characters can be viewed on the screen >in 2003 and earlier in 2007 that is 32,768. ... >5. Although you CAN concatenate two cell with 1024 characters into >one cell, you can't see the results because of item #3 above. ... You *should* know better than simply spouting online help because Excel's online help is often quite inaccurate. This is one of those cases. Depending on type face, point size and the characters involved, Excel 2003 can display more than 1200 characters in a single word wrapped cell with large column width and row height. And, as others have already pointed out, adding newlines to text strings allows Excel to display far more than 1023 chars in a single cell. At 1024x768 screen resolution, 143.0 column width and 409.0 row height, in 3 point Arial Narrow type face, Excel 2003 can display at least 32709 characters built from the formulas A1 (remove newlines): ----+----A----+----B----+----C----+----D----+----E----+----F ----+----G----+----H----+----I----+----J----+----K----+----L ----+----M----+----N----+----O----+----P----+----Q----+----R ----+----S----+----T A2: =A1&A1&LEFT(A1,80)&CHAR(10) A3 (this is the one!); =A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2 &A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2 &A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2&A2 &A2&SUBSTITUTE(A2,CHAR(10),"__") At the very least, Excel's online help needs a few caveats. Perhaps you won't mindlessly repeat it in future. First of all, why does the workbook require hundreds of sentences? Can't you do a mail merge for this? Secondly, you can use multiple cells. Type in all but the last sentence in A1. Type in this formula in A2: ="In fact, it exceeded all expectations and was: "&ROI You can store the sentences in ranges on other worksheets or in defined names. You can use this array formula to find the text of Sentence1 (a defined range in another worksheet, containing the sentence above) following the last period, and insert the value of ROI after it, so as not have to do this manually: =TRIM(RIGHT(Sentence1,MATCH(TRUE,MID(Sentence1,LEN(Sentence1)+1- ROW(INDIRECT("1:"&LEN(Sentence1))),1)=".",0)-1))&" "&ROI If you have the right template and set of rules, Excel will do fine for text processing. If you have any sentence that's too long for Excel (as a string value, not formula), consider rewriting them for clarity. I would go ahead and say that the sentence you provided for your example is too long already for readable English, but it's not my decision so be as it may. On Jan 19, 8:59 am, bao...@my-deja.com wrote: > In Excel 2002, the longest string I can use in a formula is (to my > mind) ludicrously short. [quoted text clipped - 26 lines] > > *** The only thing missing from this thread is that db troll extolling the virtues of why this should be done in a db.(what's his name, Aaron?) SignatureBiff Microsoft Excel MVP > In Excel 2002, the longest string I can use in a formula is (to my > mind) ludicrously short. [quoted text clipped - 26 lines] > > *** "T. Valko" <biffinp...@comcast.net> wrote... >The only thing missing from this thread is that db troll extolling >the virtues of why this should be done in a db. . . . Dangling red meat in front of trolls became a good idea when? Belated thanks from me, the OP, to all who built this thread. One, I hadn't thought of putting (or better hiding) the long sentence elsewhere, then simply concatenating it where I want it. Great idea. Two, y'all say Excel 2007 has increased permissible formula length to 8,192 bytes? Well, that should do me. *** Tangentially, suggesting a word processor (or resorting to programming) instead of spreadsheet software here is like, say, telling someone who complains that his Toyota has only two cup holders, to simply stay home so that the car's 3+ passengers can all set their drinks down--when the point is they all need to get somewhere. I've got a document that I've automated with many linked cells. If I change something, I want all references to it to change. If I used a word processor, my only recourse would be Search & Replace to update those n references (...er, wouldn't it?) Besides, that is a manual process, which by definition means errors will occur. For a few dozen iterations of something, that's no burden. For hundreds of references? Nope. I suspect that many, many people like myself don't USE Excel for mathematics, they don't USE it for finance & accounting, they don't USE it for numbers. They use it to keep LISTS, they use it for COLUMNAR information, they use it for OUTLINEs, and importantly, for automatic referencing capability. Which of course involves formulas. My initial feeling was that, in the 21st century, on a 32-bit--nay, 64- bit--system, it struck me that limiting the length of ANYTHING the user wants to do on a computer to mere kilobytes is a joke. But, I realize that apps have to have their limits. And I think many of us will breathe a lot easier with 8,192 bytes, which is far larger than any English paragraph. And I repeat that Excel is, for me, the most superb, rich-featured app ever created. Thanks all again. *** |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.