Is there any way to use longer strings in formulas?

B

baobob

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.

***
 
C

charabeuh

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
 
D

Dave Peterson

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

Bernard Liengme

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
 
H

Harlan Grove

Bernard Liengme said:
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
Click to expand...
....

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

Craig Schiller

Harlan said:
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?
Click to expand...
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
 
H

Harlan Grove

Craig Schiller said:
There are programs available to handle longish text strings. They're
called word processors.
Click to expand...

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
 
C

Craig Schiller

Frankly, I wouldn't try Word, with or without VBA, to do anything unless
compelled to by circumstances.
 
C

Charles W Davis

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

Dave Peterson

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).
 
H

Harlan Grove

Craig Schiller said:
Frankly, I wouldn't try Word, with or without VBA, to do anything
unless compelled to by circumstances.

Harlan Grove wrote: ....
Click to expand...

OK, I'll take that as an admission that you now recognize the
difference between text processing and word processing.
 
T

Tyro

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
 
Top