Is there any way to use longer strings in formulas?

  • Thread starter Thread starter baobob
  • Start date Start date
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.

***
 
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
 
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.
 
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
 
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?
 
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
 
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
 
Frankly, I wouldn't try Word, with or without VBA, to do anything unless
compelled to by circumstances.
 
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.
 
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).
 
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.
 
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
 
Back
Top