# Contents of cell in Excel is obviously too long

In A1, I have Fat Bill Smith
In A2, I have Fat Bill' Beef Jerky.
In A3, which is a report paragraph i have the following...
="We have compiled the accompanying Calculation of the Earnings Before Interest, Taxes, Depreciation and Amortization (EBITDA Calculation) and CalcXML Valuation Summary of the Estimated Average Value of the Company as a Whole and the Net Average Value of "&H\$10&"’s Ownership Interest as of "&H\$9&".  These reports were comprised of the procedures enumerated below and were agreed to by "&H\$7&" (hereinafter sometimes referred to as the ""Company"") and "&H\$10&", a shareholder of "&H\$7&" (hereinafter sometimes referred to as the ""Shareholder"").  We have not audited or reviewed the accompanying EBITDA Calculation and CalcXML Valuation Summary and, accordingly, do not express an opinion or any other form of assurance about whether the accompanying schedules are in accordance with accounting principles generally accepted in the United States of America."
In playing with the text, It would appear that I have exceeded some sort of length boundary.  Anyway to solve this problem without chopping up the text into multiple cells?
LVL 1
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Hi

You can split the data into different cells and use this formular

replace ? with the respective cell number say if you split that into 3 columns in A4 A5 and A6
in A3 cell you can use
=TRIM(A1)&TRIM(A2)&TRIM(A4)&TRIM(A5)&TRIM(A6)
just put your &H\$9& and other as required
Cost AccountantCommented:
Try using the SUBSTITUTE method for this rather than a long concatenation using &...

The SUBSTITUTE can change all instances of [xxxx] witha word.
Then use SUBSTITUTE as many times as you need.
I tend to use square brackets to denote a replaced field in a pices of text such as a name.
Microsoft Excel ExpertCommented:
If the abovementioned text is the text in a single cell then I don't think you have exceeded the limit.  It could be that you are missing a quote in the formula.
Cost AccountantCommented:
Example of SUBSTITUTE attached...
U--Example.xlsx
EngineerCommented:
Wherever there is a long string of text within quotes you can split it by inserting

"&"

anywhere within the string.
EngineerCommented:
So

="We have compiled the accompanying Calculation of the Earnings Before Interest, Taxes, Depreciation and Amortization (EBITDA Calculation) and CalcXML Valuation Summary of the Estimated Average Value of the Company as a Whole and the Net Average Value of "&H\$10&"’s Ownership Interest as of "&H\$9&".  These reports were comprised of the procedures enumerated below and were agreed to by "&H\$7&" (hereinafter sometimes referred to as the ""Company"") and "&H\$10&", a shareholder of "&H\$7&" (hereinafter sometimes referred to as the ""Shareholder"").  We have not audited or reviewed the accompanying EBITDA Calculation and CalcXML Valuation Summary and, accordingly, do not express an opinion or any other form of assurance about whether the accompanying schedules are in accordance with accounting principles generally accepted in the United States of America."

Can be

="We have compiled the accompanying Calculation of the Earnings Before Interest, Taxes, Depreciation and Amortization (EBITDA Calculation) and CalcXML Valuation Summary of the Estimated Average Value of the Company as a Whole and the Net Average Value of "&H\$10&"’s Ownership Interest as of "&H\$9&".  These reports were comprised of the procedures enumerated below and were agreed to by "&H\$7&" (hereinafter sometimes referred to as the ""Company"") and "&H\$10&", a shareholder of "&H\$7&" (hereinafter sometimes referred to as the ""Shareholder"").  We have not audited or reviewed the accompanying EBITDA Calculation and CalcXML"&" Valuation Summary and, accordingly, do not express an opinion or any other form of assurance about whether the accompanying schedules are in accordance with accounting principles generally accepted in the United States of America."

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
you can try this too split the info you have in each " " and in say A3 you can use this formula too
so here you don't need to use & & on either side

=CONCATENATE(A1,A2,A4,H\$10,A5,H\$9,H\$7,A6,H\$10)
Executive Managing MemberAuthor Commented:
Unfortunately, it is a size limitation (Excel 2003).  When I eliminated enough "raw" text, the problem went away.  (There were no formulas in the text I deleted.)

SUBSTITUTE AND CONCATENATE might work, but the letter requires subtle and not so subtle changes depending on the report and I will not always be the one doing the editing.

Saqib's "&" is perfect.  I can put them between sentences and warn everyone to stay away from them when editing.

Thanks again guys.  You are saving my life here!
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.