Is there any way to use longer strings in formulas?

[Harlan Grove]

(e-mail address removed) wrote...

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.

....

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.

 

[Harlan Grove]

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.

 

[Tyro]

Word has VBA

Tyro

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.

 

[Harlan Grove]

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.

 

[Tyro]

and Word itself (no VBA) isn't programmable.

...

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.

 

[Harlan Grove]

. . . 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?

 

[Tyro]

I would never use either for that. I would simply write some code in say C++
use that.

Tyro

 

[Harlan Grove]

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.]

 

[Tyro]

I was not given a C++ compiler. I own one. I've been programming for almost
45 years.

Tyro

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.]

 

[Shane Devenshire]

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

 

[Tyro]

Best to use a Word processor. Let Excel handle numbers to its limited
ability and let word processors handle text and words.

Tyro

 

[ilia]

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.

 

[T. Valko]

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?)

 

[Dave Peterson]

#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.

 

[Harlan Grove]

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.

 

[Harlan Grove]

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.

 

[Harlan Grove]

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?

 

[baobob]

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.

***