formula character limit

[ILv2Xlr8]

I'm trying to embed the text from one cell into another.

The only way I know how to do this is using a formula e.g.
="The result is" &B2& "units"

This technique gives me formula errors most likely depending on the length of the text. I initially thought the limit would be 255 characters, but I have some cells that have total character count of 305 that do not give errors?

I do not know what the character limit is or if spaces count towards the character limit or if there are forbidden characters to use as text within the quotes when using this method e.g. -,*,!,? etc.

I'm using MS Excell 2003.

Can one of you Gurus clue me in to what is going on?

 

[doofusboy]

From what I recall Excell will display up to 255 characters per cell. 256 results in the # character being displayed. The contents of the cell are still visible in the formula bar [I think that's what it's called] up to one thousand some characters [think it's around 1,055]. I don't think cells can hold more characters than that without creating an error.

 

[bosco_yip]

Try to use LEN function, to return the number of characters in a text string cell.

 

[barry houdini]

a cell can hold up to 32767 characters but only 1024 will display in the cell (certainly that true in Excel 2003 and Excel 2007). Are you referencing a cell on the same worksheet?

 

[ILv2Xlr8]

Yes I'm referencing a cell in the same sheet. Does that make a difference?

After playing around for a bit, I’m guessing the trick is a total character count of ≤255 not including the = or the initial and final quotations " ". The referenced cell count is not included in the total character counts of the formula.

Another idiosyncrasy is smart quotes copied from MS word will also cause errors.

 

[SamTYler]

(Edit: Obviously I dont)IIRC, the character limit per cell is 64KB. That includes spaces, special operators, and cell references; literally, every keystroke.

This plan might make your life easier:
Insert a new sheet next to the "texting" sheet. Name it "Phrase." Fill column A with all the sentence parts you use and sort them alphabetically. As you add new sentence part, you can resort, Excel will track the changes in references for you.

If Phrase!A1 = " " (a single space)
If Phrase!A2 = The Result is
IF Phrase!A3 = Units

Then this:
="The result is" &B2& "units"
Would be entered by putting an = sign in and clicking on Phrase!A2+& and Phrase!A1+& and B2+& and Phrase!A1+& and Phrase!A3.

When you're done, Hide sheet(Phrase")

Some advantages are that Formula Errors will show up in the formula bar; you can change every instance of a sentence part in one place; No more typos.

In place of haveing to adda space refernce to every sentence part; some parts are obviousle sentence beginnings, include the space at their ends; Some are obviously middle parts, bracket them in spaces; Some are ends, start them with a space and end them with a dot.

I would color code them somehow to remind myself which were beginnings, mids, and ends. Some you can't know til you use them, you'll add the punctuation as you build the formula.

 

[barry houdini]

OK, I think I misunderstood. You can't have a single text value in a formula that exceeds 255 characters.....but you can concatenate shorter ones together so if you need to have more than 255 characters you can do something like this

="200_character_text"&"another100characters"&"moretext"&"moretext"&B2&"even more text"