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

***
0
baobob (32)
1/19/2008 1:59:21 PM
excel 39879 articles. 2 followers. Follow

37 Replies
601 Views

Similar Articles

[PageSpeed] 44

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







<baobob@my-deja.com> a �crit dans le message de news:8bf78b66-cc38-4174-9452-72fa21019b3e@i29g2000prf.googlegroups.com...
> 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.
>
> *** 

0
1/19/2008 2:24:03 PM
TEXTBOX

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
<baobob@my-deja.com> wrote in message 
news:8bf78b66-cc38-4174-9452-72fa21019b3e@i29g2000prf.googlegroups.com...
> 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.
>
> *** 

0
dguillett1 (2487)
1/19/2008 2:25:35 PM
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.

baobob@my-deja.com 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.
> 
> 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.
> 
> ***

-- 

Dave Peterson
0
petersod (12004)
1/19/2008 2:27:42 PM
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 V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

<baobob@my-deja.com> wrote in message 
news:8bf78b66-cc38-4174-9452-72fa21019b3e@i29g2000prf.googlegroups.com...
> 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.
>
> *** 


0
bliengme5824 (3040)
1/19/2008 5:14:52 PM
"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?
0
hrlngrv1 (375)
1/19/2008 8:38:49 PM
This is a multi-part message in MIME format.
--------------050908040208060408040505
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Harlan Grove wrote:

>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

--------------050908040208060408040505
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Harlan Grove wrote:
<blockquote
 cite="mid1bce9bc6-fb6b-428c-85d8-87cde298b2cb@s8g2000prg.googlegroups.com"
 type="cite">
  <pre wrap="">But just to be argumentative, if Excel weren't meant to handle
paragraphs, why does it include Edit &gt; Fill &gt; Justify or Format &gt;
Alignment &gt; Wrap Text? What purpose do either serve if not to handle
longish text strings?
  </pre>
</blockquote>
<u>Not</u> to be argumentative <span class="moz-smiley-s1"><span> :-) </span></span>,&nbsp;
but I most often use Wrap Text to allow for two or more lines of text
in a column heading. <br>
<br>
There are programs available to handle longish text strings. They're
called word processors.<br>
<br>
Craig<br>
</body>
</html>

--------------050908040208060408040505--
0
cschiller1 (102)
1/19/2008 10:53:37 PM
Use Word not Excel

Tyro

<baobob@my-deja.com> wrote in message 
news:8bf78b66-cc38-4174-9452-72fa21019b3e@i29g2000prf.googlegroups.com...
> 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.
>
> *** 


0
Tyro (331)
1/20/2008 1:10:27 AM
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
0
hrlngrv1 (375)
1/20/2008 1:57:52 AM
This is a multi-part message in MIME format.
--------------010902050101070403000103
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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

Harlan Grove wrote:

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

--------------010902050101070403000103
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Frankly, I wouldn't try Word, with or without VBA, to do anything
unless compelled to by circumstances.<br>
<br>
Harlan Grove wrote:
<blockquote
 cite="midb04255d4-7c69-4177-ad9c-b7c32dda085e@p69g2000hsa.googlegroups.com"
 type="cite">
  <pre wrap="">Craig Schiller <a class="moz-txt-link-rfc2396E" href="mailto:cschill...@earthlink.net">&lt;cschill...@earthlink.net&gt;</a> wrote...
....
  </pre>
  <blockquote type="cite">
    <pre wrap="">There are programs available to handle longish text strings. They're
called word processors.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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
  </pre>
</blockquote>
</body>
</html>

--------------010902050101070403000103--
0
cschiller1 (102)
1/20/2008 5:05:47 PM
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.

"Tyro" <Tyro@hotmail.com> wrote in message 
news:8cxkj.581$hI1.167@nlpi061.nbdc.sbc.com...
> Use Word not Excel
>
> Tyro
>
> <baobob@my-deja.com> wrote in message 
> news:8bf78b66-cc38-4174-9452-72fa21019b3e@i29g2000prf.googlegroups.com...
>> 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.
>>
>> ***
>
> 

0
Anthemwebs (309)
1/20/2008 7:27:21 PM
I have Excel 2007. The OP does not. But Excel is not a word processing 
program.

Tyro

"Charles W Davis" <Anthemwebs@lvcoxmail.com> wrote in message 
news:BBEDACA8-04FE-4D37-98C9-ED7336EED6F3@microsoft.com...
> 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.
>
> "Tyro" <Tyro@hotmail.com> wrote in message 
> news:8cxkj.581$hI1.167@nlpi061.nbdc.sbc.com...
>> Use Word not Excel
>>
>> Tyro
>>
>> <baobob@my-deja.com> wrote in message 
>> news:8bf78b66-cc38-4174-9452-72fa21019b3e@i29g2000prf.googlegroups.com...
>>> 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.
>>>
>>> ***
>>
>>
> 


0
Tyro (331)
1/20/2008 7:34:16 PM
Also, 2007 provides: Length of formula contents: 8,192 characters
"Charles W Davis" <Anthemwebs@lvcoxmail.com> wrote in message 
news:BBEDACA8-04FE-4D37-98C9-ED7336EED6F3@microsoft.com...
> 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.
>
> "Tyro" <Tyro@hotmail.com> wrote in message 
> news:8cxkj.581$hI1.167@nlpi061.nbdc.sbc.com...
>> Use Word not Excel
>>
>> Tyro
>>
>> <baobob@my-deja.com> wrote in message 
>> news:8bf78b66-cc38-4174-9452-72fa21019b3e@i29g2000prf.googlegroups.com...
>>> 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.
>>>
>>> ***
>>
>>
> 

0
Anthemwebs (309)
1/20/2008 7:41:01 PM
The OP has Excel 2002. In any event, Excel is not a word processing program. 
Word is.

"Charles W Davis" <Anthemwebs@lvcoxmail.com> wrote in message 
news:A8AF7865-02A0-4DE0-B99F-D3056E288444@microsoft.com...
> Also, 2007 provides: Length of formula contents: 8,192 characters
> "Charles W Davis" <Anthemwebs@lvcoxmail.com> wrote in message 
> news:BBEDACA8-04FE-4D37-98C9-ED7336EED6F3@microsoft.com...
>> 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.
>>
>> "Tyro" <Tyro@hotmail.com> wrote in message 
>> news:8cxkj.581$hI1.167@nlpi061.nbdc.sbc.com...
>>> Use Word not Excel
>>>
>>> Tyro
>>>
>>> <baobob@my-deja.com> wrote in message 
>>> news:8bf78b66-cc38-4174-9452-72fa21019b3e@i29g2000prf.googlegroups.com...
>>>> 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.
>>>>
>>>> ***
>>>
>>>
>>
> 


0
Tyro (331)
1/20/2008 8:28:06 PM
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
0
pgrebug (2)
1/20/2008 9:11:12 PM
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).

Charles W Davis wrote:
> 
> 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.
> 
> "Tyro" <Tyro@hotmail.com> wrote in message
> news:8cxkj.581$hI1.167@nlpi061.nbdc.sbc.com...
> > Use Word not Excel
> >
> > Tyro
> >
> > <baobob@my-deja.com> wrote in message
> > news:8bf78b66-cc38-4174-9452-72fa21019b3e@i29g2000prf.googlegroups.com...
> >> 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.
> >>
> >> ***
> >
> >

-- 

Dave Peterson
0
petersod (12004)
1/20/2008 9:12:30 PM
Craig Schiller <cschill...@earthlink.net> wrote...
>Frankly, I wouldn't try Word, with or without VBA, to do anything
>unless compelled to by circumstances.
>
>Harlan Grove wrote:
....
>>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.
0
hrlngrv1 (375)
1/20/2008 9:16:58 PM
"Tyro" <T...@hotmail.com> wrote...
>Use Word not Excel
....

A clear sign you don't understand the OP's question.
0
hrlngrv1 (375)
1/20/2008 9:18:07 PM
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

"Peter G" <pgrebug@inbox.com> wrote in message 
news:903b15a5-32de-481f-a78c-5719723fe02f@v4g2000hsf.googlegroups.com...
>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 


0
Tyro (331)
1/20/2008 9:21:43 PM
This is a multi-part message in MIME format.
--------------060900060401040406060103
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Whatever you say, Harlan. Excel shouldn't be used for either.

Harlan Grove wrote:

>Craig Schiller <cschill...@earthlink.net> wrote...
>  
>
>>Frankly, I wouldn't try Word, with or without VBA, to do anything
>>unless compelled to by circumstances.
>>
>>Harlan Grove wrote:
>>    
>>
>...
>  
>
>>>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.
>  
>

--------------060900060401040406060103
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Whatever you say, Harlan. Excel shouldn't be used for either.<br>
<br>
Harlan Grove wrote:
<blockquote
 cite="midf4ac74ab-6ec1-4c6d-a570-812d4c10c072@f47g2000hsd.googlegroups.com"
 type="cite">
  <pre wrap="">Craig Schiller <a class="moz-txt-link-rfc2396E" href="mailto:cschill...@earthlink.net">&lt;cschill...@earthlink.net&gt;</a> wrote...
  </pre>
  <blockquote type="cite">
    <pre wrap="">Frankly, I wouldn't try Word, with or without VBA, to do anything
unless compelled to by circumstances.

Harlan Grove wrote:
    </pre>
  </blockquote>
  <pre wrap=""><!---->...
  </pre>
  <blockquote type="cite">
    <blockquote type="cite">
      <pre wrap="">Text processing and word processing aren't the same thing. . . .
      </pre>
    </blockquote>
  </blockquote>
  <pre wrap=""><!---->
OK, I'll take that as an admission that you now recognize the
difference between text processing and word processing.
  </pre>
</blockquote>
</body>
</html>

--------------060900060401040406060103--
0
cschiller1 (102)
1/20/2008 10:30:03 PM
bao...@my-deja.com 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.
0
hrlngrv1 (375)
1/20/2008 10:34:14 PM
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.
0
hrlngrv1 (375)
1/20/2008 10:41:31 PM
Word has VBA

Tyro

"Harlan Grove" <hrlngrv@gmail.com> wrote in message 
news:ef9d4f24-f4c4-49f8-9166-9010e6099f67@e25g2000prg.googlegroups.com...
> 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. 


0
Tyro (331)
1/20/2008 10:50:45 PM
"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.
0
hrlngrv1 (375)
1/20/2008 11:23:58 PM
and Word itself (no VBA) isn't programmable.

"Harlan Grove" <hrlngrv@gmail.com> wrote in message 
news:0bbb287e-bbfd-4d9b-8446-9e89dffe542f@j20g2000hsi.googlegroups.com...
> "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. 


0
Tyro (331)
1/20/2008 11:27:28 PM
"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?
0
hrlngrv1 (375)
1/20/2008 11:28:20 PM
I would never use either for that. I would simply write some code in say C++ 
use that.

Tyro

"Harlan Grove" <hrlngrv@gmail.com> wrote in message 
news:e75fb4ff-0003-4b99-a6d0-899d61a382dd@i7g2000prf.googlegroups.com...
> "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? 


0
Tyro (331)
1/20/2008 11:31:37 PM
"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.]
0
hrlngrv1 (375)
1/21/2008 1:00:43 AM
I was not given a C++ compiler. I own one. I've been programming for almost 
45 years.

Tyro

"Harlan Grove" <hrlngrv@gmail.com> wrote in message 
news:f9fddfb4-03fb-4a57-9a84-a1a01097b9a6@s8g2000prg.googlegroups.com...
> "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.] 


0
Tyro (331)
1/21/2008 2:44:58 AM
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


<baobob@my-deja.com> wrote in message 
news:8bf78b66-cc38-4174-9452-72fa21019b3e@i29g2000prf.googlegroups.com...
> 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.
>
> *** 

0
1/21/2008 4:35:28 AM
Best to use a Word processor. Let Excel handle numbers to its limited 
ability and let word processors handle text and words.

Tyro
"Shane Devenshire" <shanedevenshire@sbcglobal.net> wrote in message 
news:%232KFSc%23WIHA.4712@TK2MSFTNGP04.phx.gbl...
> 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
>
>
> <baobob@my-deja.com> wrote in message 
> news:8bf78b66-cc38-4174-9452-72fa21019b3e@i29g2000prf.googlegroups.com...
>> 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.
>>
>> ***
> 


0
Tyro (331)
1/21/2008 4:51:14 AM
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:

=3D"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:

=3DTRIM(RIGHT(Sentence1,MATCH(TRUE,MID(Sentence1,LEN(Sentence1)+1-
ROW(INDIRECT("1:"&LEN(Sentence1))),1)=3D".",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=A0am, bao...@my-deja.com 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:
>
> =3D"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.
>
> ***

0
iasafiev (57)
1/21/2008 5:37:03 AM
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?)

-- 
Biff
Microsoft Excel MVP


<baobob@my-deja.com> wrote in message 
news:8bf78b66-cc38-4174-9452-72fa21019b3e@i29g2000prf.googlegroups.com...
> 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.
>
> *** 


0
biffinpitt (3171)
1/21/2008 7:56:25 AM
#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.

Shane Devenshire wrote:
> 
> 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
> 
> <baobob@my-deja.com> wrote in message
> news:8bf78b66-cc38-4174-9452-72fa21019b3e@i29g2000prf.googlegroups.com...
> > 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.
> >
> > ***

-- 

Dave Peterson
0
petersod (12004)
1/21/2008 11:47:13 AM
"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.
0
hrlngrv1 (375)
1/21/2008 9:52:15 PM
"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.
0
hrlngrv1 (375)
1/21/2008 10:45:49 PM
"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?
0
hrlngrv1 (375)
1/21/2008 10:47:33 PM
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.

***
0
baobob (32)
1/28/2008 10:58:23 PM
Reply:

Similar Artilces:

Best Way ?
G'day All, Looking for some information. With Outlook you can have all your contacts...address, email address, yada, yada, yada. Can I use this information in to say mail merge with Word and email a letter to the ppl with email addresses? Or is this better done with a Access? The reason I ask is that we want to deliver an email to all our clients with the latest promotions. And want it to be a bit more automated? Any information or pointers in the right direction would be appreciated. Have a good one, Scott V You can use either Access or Outlook as the data source for a ma...

'Recently used file' list.
When I open Excel nine filenames appear on the RHS as 'recently used files' (this number was set via Tools/Options/General, of course). In the middle of this list are 3 no-longer-used files. Though the files themselves have been deleted their names remain in this list. Is there an easy way to remove those entries from that list, please? TIA, DB. There is an entry in the help file "Display or hide recently used workbooks on the File menu" which says uncheck the 'recently used files' checkbox, the click OK. Then, basically, start again. "DB.&...

Selecting and moving chart(s) within a worksheet using keyboard ke
Hi all, I have created some charts in a worksheet. I would like to be able to do the following if possible: 1. I have selected a chart (by clicking on the chart and handles appeared around the perimeter of the chart). What I would like to do is to be able to select the chart using keyboard short-cut and to move the chart around the worksheet also using keyboard keys rather than the mouse. At the moment I can only move it (a single chart) using the mouse. However strangely enough, if I select two or more charts (Shift+ Lt.Click) I am able to then use the arrow keys to move the chart. ...

Is there a way to color code Outlook Contact File Folder Labels?
Is there a way to color code Outlook Contact File Folder Labels? No. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Join OneNote Tips mailing list: http://www.onenote-tips.net/ "isailhbca" <isailhbca@discussions.microsoft.com> wrote in message news:4DBD10F7-FAA0-4102-A4C3-E2697908EFE8@microsoft.com... >...

A way to create sub-categories?
I want to group contacts by category and then a sub-category for easy viewing. Apart from creating a new field for sub-category is there a way of doing this? Outlook doesn't seem to support sub-categories. Thanks Nope. -- Patricia Cardoza Outlook MVP Author - Special Edition Using Microsoft Office Outlook 2003 Lead Author - Access 2003 VBA Programmer's Reference Author - Absolute Beginner's Guide to Microsoft OneNote 2003 http://blogs.officezealot.com/cardoza http://www.cardozasolutions.com "Casebase" <junkmail@casebase.net> wrote in message news:%23rx5a...

Newsreader when using Outlook
I'm just transition to Outlook (from OE). Just wondering about news-readers. I use OE as a news-reader at the moment. But it's not apparent that that is possible in Outlook proper. If this is so - what are Outlook user's doing in this reagards? Many thanks. === Phil (NZ) You are in luck. OE is the news reader for Outlook. Always has been. Always will be. -- Russ Valentine [MVP-Outlook] "Phil Jones" <phil_newsgroup@hotmail.com> wrote in message news:%23lEn6sibFHA.2756@tk2msftngp13.phx.gbl... > I'm just transition to Outlook (from OE). > > Just...

is there a way to crop a corner out of a rectangular image in Visi
Using subtract on two shapes accomplishes what i want to do, but when the first shape is a .gif image, it does not work. No, the "boolean operations", as they're called, do not operate on foreign objects like bitmaps and controls. Visio has crop functionality for imported graphics, but it is only rectangular. You could draw a small white triangle and give it "no line". Then place it over the corner of your bitmap. -- Hope this helps, Chris Roth Visio MVP "doug" <doug@discussions.microsoft.com> wrote in message news:29D479A6-6099-4051-B7A0-1...

Help with custom search form
I have a search form that retrieves records in a subform based on criteria entered into unbound text boxes on the main/parent form. The subform is tied to a SQL recordset that contains a field called “Select”. The field’s datatype is Yes/No. On the main form I have a button called “View Selected” with the following code in the OnClick event: Private Sub cmdViewSelected_Click() On Error GoTo Err_cmdViewSelected_Click Dim i As Integer i = DCount("ProjectId", "ProjectTbl", "[Select] = " & True) If i = 0 Then MsgBox "No Record(s...

Use tiltle in a cell
Is there a way to use the title of the spreadsheet as a cell value? I asked this before in another topic, but there was no reply. My sheet get's it's title from a database and is variable. So if i can use the title i can strip several characters from the title and use this. So I want to retrieve the title of the spreadsheet and use this in a cell. Thanks! The name of a worksheet can be extracted to a cell with: =MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from em...

Formula Does not work on excel 2003
This formula does not work in excel 2003. Does anyone know how to convert it to the correct formula for excel 2003. It works perfectly on my system with 2007. But when I put it on a system with 2003 it returns nothing. What it is supposed to do is pull data from a sheet name in the format dd.mm.yy. Sometimes the sheet is not there so should return nothing or 0. =IF(ISERROR(INDIRECT("'"&TEXT($A4,"dd-mm-yy")&"'!$k$12")),"",INDIRECT("'"&TEXT($A4,"dd-mm-yy")&"'!$k$12")) Your formula work...

is there a way to change the time card repor format?
i'm using activereports in RMS to generate a custome timecard report. for my timein and timeout colums, the datatype i have assigned to the columns is vbDate. when I generate the report, the columns only show the date as mm/dd/yyyy, they do not show the time of day piece for exactly when the cashier punched in or out. is there any way to change the formatting of this so that in the report the time shows up? for example, instead of showing 2/6/2005 in the time in column, it would show 2/5/2005 8:00:00 AM. thanks in advance, kevin There is a timecard report on the Reports Library...

Why is excel 2007 slow when selecting chart data from the formula
Excel 2007 is very slow when selecting chart data from the formula bar. For example 8, X-Y scatter graph curves are generated from data base of approximately 2000 lines long x 2 columns. A convienient way to edit the cell references for each curve is to simply click onto a curve and the cell references will appear in the formula bar. With all earlier versions of excel including 2003, once the curve is clicked the cell reference appears in the formula bar almost instantaneously. However when the same method is used with excel 2007, it can take 10-20 seconds before the formular appears. ...

Using DoCmd.SendObject
Hi, My default e-mail client is outlook2007 and configured to connect to an Exchange server.Is there any possibility of DoCmd.SendObject open Vista Windows Mail instead of Outlook 2007? If no, can someone tell me another procedure to do that. Thanks CJA Tony Toews has some good info here about various approaches to sending email from Access: http://www.granite.ab.ca/access/email.htm -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "CJA" <car...

best way to deserialize xml
I'm at a quandry as to what's the best/fastest way to deserialize an incoming xml stream? I ran a small test on xmlserializer and on a small file it performed 10 times worse then when I used the XMLDocument and deserialized the values myself. Is the xmlserializer really that slow? I'm expecting a file about 1 MB in length and if it's that slow on a 2 K file then I can't phantom how it's going to behave on a 1 MB file. Should I just skip the xmlserializer and XML Dom and go straight to xmlreader and do the deserialization myself? This has been discussed a few times...

Can You Count This Way?
I have some cells with note type data: For ex: ax,3b7,22,xy,zz, Is there a way to count every item between the commas? Thanks. Hi try =LEN(A1)-LEN(SUBSTITUTE(A1,",","")+LEN(A1)>0 -- Regards Frank Kabel Frankfurt, Germany Timothy Wright wrote: > I have some cells with note type data: > > For ex: > > ax,3b7,22,xy,zz, > > Is there a way to count every item between the commas? > > Thanks. On Wed, 19 May 2004 20:13:53 +0200, "Frank Kabel" <frank.kabel@freenet.de> wrote: >Hi >try >=LEN(A1)-LEN(SUBSTITUTE(A1,&q...

Uses for Autotext Cleanup
Version: 2004 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Email Client: Exchange Hello, <br><br>I'm still very new to Entourage and I'm looking for some tangible reasons to use the Autotext Cleanup in 2004. Do people really use it? <br><br>I would like to pass these examples on to staff. <br><br>Thanks for your help robomac@officeformac.com wrote: > I'm still very new to Entourage and I'm looking for some tangible > reasons to use the Autotext Cleanup in 2004. Do people really use it? > > I would like ...

How to generate a mailbox list file to use in scheduled ExMerge
Hi! Anyone know how I can generate a file that contains a list of mailboxes to be used with scheduled ExMerge? I tried to interactively run ExMerge, then cancell the program halfway, but it does not create the file I need. An alternative for creating the file would be to use an LDAP query in a script -- if anyone can help with that as well. Thanks MosesM On Wed, 4 Jan 2006 11:05:04 -0800, MosesM <MosesM@discussions.microsoft.com> wrote: >Hi! Anyone know how I can generate a file that contains a list of mailboxes >to be used with scheduled ExMerge? I tried to interactively ru...

Is there a way to Sort data by color in Excel?
I am trying to sort data by color, is that possible? see http://www.xldynamic.com/source/xld.ColourCounter.html#sorting -- HTH Bob Phillips "SRTUC" <SRTUC@discussions.microsoft.com> wrote in message news:B5121232-FB1D-46AF-94F8-04B7EAA2D90C@microsoft.com... > I am trying to sort data by color, is that possible? You're gonna need a User defined function to get the value of a color. See Chip Pearson's site for a nice version: http://www.cpearson.com/excel/colors.htm Look for: Returning The ColorIndex Of A Cell Then you could use that UDF in a separate colu...

Using OE with my Juno e-mail account
I use Juno e-mail but I noticed that I also have Outlook Express on my computer which I would prefer to use. Is there a way to send and receive e-mail using OE but keeping my Juno address and service? I have a standard dial-up connection. OS is Win98SE. If this is possible, I would need simple, step-by-step instructions for setting it up. Thank you, Relztrah Try posting this in an Outlook Express news group - this is not one of them. Outlook is a part of Microsoft Office and is what this group supports. Outlook Express is a part of Internet Explorer and has its own news groups. You ca...

Microsoft Office on Mac using Bootcamp or Microsoft for Mac products?
I'm a new Mac user - just got a new Intel Core 2 Duo processor MacBook. I went ahead and bought the Office for Mac and installed everything. My main problem/question is that I find I can't do as many things in Entourage as I could in Outlook. (For instance, can't change colors and customize calendar as well, can't set up as many defaults like what view I want Entourage to open in.) I was thinking about using bootcamp and trying to go back to my Outlook I used to love. Any advice as to whether this is a good idea or bad idea? Also, keep in mind that I am synching successful...

Data Validation, Via A Formula
Is there a way to apply data validation to one cell, based on the result of another cell? For instance, if in A1, a data validation list displays sports (ie baseball, basketball,etc), and if the user selects "Baseball", then the data validation in B1 would only list baseball teams, from the range named baseball teams. And if basketball is selected, then only basketball teams are displayed. If at all possible, could this be done via a formula and WITHOUT using VB? If not, I could also use any help to accomplish this via VB coding. Any and all help will be appreciated...

Sizing the Chart Sheet using VBA
Hi all, I'm attempting some code that will take the height and width values from an active Word table cell, and then use them to adjust the page margins of an active Excel chart sheet, with the end result being both the Word cell and Excel chart sheet share identical dimensions. Unfortunately, the code seems a little beyond me. Is it possible? Any help (or code) much appreciated ... Best wishes to all, Rich ...

Fastest way to load an xml file into an array in .net
Hi All VS 2005 SP1 (C#, VB.NET) I need the absolut fastest way of loading an xml-file of about 700KB into an array. I need to load about 1500 of those xml-Files into arrays on startup of my application. Therefore it needs to be as fast as possible. Please also tell me if the xml structure can be improved. the xml looks like: y: Year m: Month h: Hour value <data> <v y="1998" m="1" h="1">0.227777109151971</v> <v y="1998" m="1" h="2">0.206573903224972</v> <v y="1998" m="1"...

Using dates with hyperlinks
i am using a very large spreadsheet with hyper links to related word files. These hyperlinks are created automatically based on cell contents. but i am having trouble when using a date formatted cell. currently i am using the format ddmmyy, but when it is used as i hyperlink, its reverts back to the serial number. is there any way to stop this? hope i explained that right! Try creating a folder on your desktop and dumping the word files into it. I have a lawn mower and I would not use it to paint a house. "jeegna" wrote: > i am using a very large spreadsheet with hyper ...

Is there a convenient way to save data
Is there a convenient way to save dialog data so it remembers the users last selection? I have a dialog based application that only has three user variables all of which are ints. What is the best way to make theses variables persistent for a given user on a given machine? Thanks "Jeff B" <jb@nospan.net> wrote in message news:fpDif.10451$Ay6.2336@fe13.lga... > > Is there a convenient way to save dialog data so it remembers the users last > selection? I have a dialog based application that only has three user > variables all of which are ints. What is the best w...