Text cell fills with asterisks

Topic

New Reply

I searched the archives and didn’t find an answer to this, am hoping someone knows. I am only an occasional Excel user, so I hope this isn’t a dumb question.

Excel 2000 9.0.3821 SR 1
I am pasting text into a cell, and when it reaches a certain maximum, the cell displays asterisks all across. The cell is formatted as text, not one of the number formats. The contents of the cell are still visible in the ‘edit box’. I have tried deleting hard returns, in case there are some invisible characters in there. Also tried adjusting the row height. Tried “paste special” and this put the data in multiple rows. Pasted that into the single cell, and the same thing – asterisks- happens.

I would be most grateful if anyone could shed some light on this. Thanks in advance.

Reply | Quote

Replies

You might give this a try:

Right click on the Cell in question, Select Format Cells. Under the ‘Alignment’ Tab, click on ‘Wrap Text.’

The chances are, this will explode your cell to show the entire contents, (by adjusting row height.)

Hope this helps!

Drk.

Reply | Quote

I forgot to mention, wrap text is already on.

Reply | Quote

How many characters are there in the ‘problem cell?’

Drk.

Reply | Quote

A few possibilites come to mind:

A, A font problem? I doubt it, but I am thinking outside the box… or,
B, You missed a ‘hidden’ character. You can try copying the text, importing to Word, Save As Plain Text, open in notepad, and paste to your cell…

Hope this helps! –you’re right, MSKB was of NO help on this problem..

Drk.

Reply | Quote

I think I see where you’re headed. There are over 300 visible characters- who knows when you count invisible ones like returns. And I wouldn’t want to try counting them again. My little pea brain doesn’t do well at this sort of thing.

I copied the cell to another location and started deleting characters. And at a certain point, all of the text showed up. Then when I typed more in, the asterisks came back.

Apparently there is a maximum capacity. I wonder if there’s a way to get around that.

Thanks for your help.

Reply | Quote

Q264626 gives the full spec on Excel 2K.
It appears 1,024 is the maximum number of characters than can be displayed in a cell.

Reply | Quote

I just put well over 2,000 characters in a single cell in Excel 2000, and did not have the problem you are describing. Does this cell have any conditional formatting set? Is there any data validation? Is the cell by any chance a merged cell?

Reply | Quote

No conditional formatting, and it’s not a merged cell. I’m not sure what you mean by data validation – there’s no formula applied to the cell.

Reply | Quote

Data validation is using the Validation command on the Data menu to test the value in the cell as meeting the criteria you set up.

Reply | Quote

Thanks for the tip. I set Data Validation to Any value and the behavior persists.

Reply | Quote

if you *re-type* your contents in a cell does the problem still occur? You must have hidden char’s in there somewhere.. check out HTML-Kit to see ‘hidden’ characters in a text editor.

Drk.

Reply | Quote

In another cell enter
=LEN(A1)
where ‘A1’ is the cell in question.
I think this should give you the number of characters in the cell.

Reply | Quote

Excel’s behaviour seems a little odd….

I have just filled a cell with 1300+ characters in XL2K, and LEN(xx) reports 1,318 characters. Adding my test string again, LEN(xx) reports 1,421 characters.

Excel only displays the first 1000-odd characters, due to the 1,024 character limitaiton you alluded to, but RIGHT(xx,10) displays the last ten characters as entered in the cell, not the last ten displayed. Functions like FIND() or MID() are able to access and return values from characters in the cell, even the ones after the 1,024 characters displayed. In any event, the cell never displayed asterisks or number signs – it just displays the characters as entered, up to the limit of 1,024.

Even with some non-printing characters (hard carriage returns) I have not been able to duplicate the behaviour originally reported. Maybe I just got a working copy of XL2K?

Reply | Quote

You could use the function =CLEAN() on the offending cell, which removes any non-prinable characters

Andrew C

Reply | Quote

I must be a little dense about using functions. (As I said in my first post, I’m just an occasional Excel user.) I place my cursor in the adjacent empty cell, go to the Fx key on the toolbar, select LEN from the list, and either navigate to or type in the value of the cell I want to know the length of. The result – in the cell in which the function formula resides is literally =(LEN)o11. Hmmm. I must be doing something wrong.

Reply | Quote

That formula should read =LEN(O11), perhaps you placed the brackets in the wrong place .

Reply | Quote

Checked the syntax of the formula and it looks right to me. It says LEN=(O11). And that’s the letter o, not zero. So still stuck on that one – means I can’t use the CLEAN function.

The clean function may be necessary to solving this problem, because I may have recalled an important hint. This problem only occurs when I paste text into a cell. I am pasting data from various emails and Word docs. It’s actually a bit of a pain because where there’s a hard return it pastes the data into separate cells. Using Paste Special and selecting unicode doesn’t help.

I’m beginning to believe it’s cursed! No, I know, it’s probably just me…

Reply | Quote

The syntax definitely does not look right to me. It should be =Len(O11) NOT Len=(O11). Move the equal sign in front of Len.

Reply | Quote

Well, after fiddling with this for a while, I’ve changed my tune. I was thinking it was just a formatting problem with pasted data, lurking special characters, but now I think it’s a bug. Here’s why. I was working with a cell. Everything looked fine. This was the data:

2/28 CB We are not able to make changes to this list. Not in the list of lists the local administrator can modify.
Then I typed in the data (not pasted).
3/1 We also need to write a proc for modifying the list of years in the pull down for the date picker (do we still have to do this if we have the java calendar?).
Asterisks display! Notice the pattern – numbers beginning a row. Yes, the cell is formatted as text. Tried updating the cell by adding quotes at beginning and end. No dice. All asterisks.

Reply | Quote

Well, I can copy that text and paste it into a cell or type it into a cell, and it displays just what I would expect it to. If you open a new workbook, can you put that text into a cell in the new workbook?

One other thing for you to check. Select Options from the Tools menu and then click on the “Transition” tab. In that dialog box, make sure that none of the boxes labeled “Transition navigation keys,” “Transition formula evaluation,” or “Transition formula entry” are checked. You do NOT want check in any of those options.

Reply | Quote

Try setting the format to General rather than Text

Andrew C

Reply | Quote

Yes, Andrew, you win the prize! Setting the format to General does the trick. Thank you!

Reply | Quote

Look at all the time!!! Great work Andrew… Think OUTSIDE the big, ugly, complicated box..

Drk.

Reply | Quote

Glad it worked.

It seems the TEXT formating in Excel does not like strings larger than 255 characters.

Regards,
Andrew C

Reply | Quote

At a job interview yesterday, the interviewer described a problem she has with the limitation on size of cells formatted for text. This morning, I found your elegantly simple solution to her problem, and emailed her to share the wealth.

I won’t find out for a least a couple of days whether I got the job, but you may very well have given me the edge over other candidates.

THANK YOU!!!!

Reply | Quote

Well thank you Anne,

Best wishes with your job application, and I am sure you are way ahead of the competition.

Andrew C

Reply | Quote