Overlapping Text vs. ### in Excel

This is probably a very basic Excel question, but I just can't find the answer anywhere!  If I type a word into cell A1 and make column A very narrow, the text overlaps into cell B1 (which is what I want).  Now if I add a number to cell A2, it does not overlap into cell B2 but shows up as ###.  What is the difference in the formatting of these two cells?  Thanks!
bonbonbon_jazzAsked:
Who is Participating?
 
xaniusCommented:
Kevin, I think you're wrong this time - or at least interpreting the question in a diferent way than I do.

I'v understood the follwing:

a) The A-Column is very narrow.
b) In A1 there is some text overlapping B1
c)in A2 ther is sume number not overlapping B2
d) all other cells in the workbook are empty.

Under this circumstances, the excel behavious is fully normal and - at least from the view of the developers of excel - expected.

Cheers
Xanius
0
 
zorvek (Kevin Jones)ConsultantCommented:
All other things equal this is not expected behavior. Can you upload your workbook so we can take a look?

Files can be posted any of a number of ways.

The prefered method at this time is to use the Experts Exchange file sharing site. It is available to all registered Experts Exchange members and it is free. First zip the file or files to be posted. Then navigate to http://www.ee-stuff.com, click Login in the upper right corner, enter your Experts Exhange user name and password, click the Login command button, navigate to Expert Area, and click "Upload a new file". Enter the complete URL of the question, or the Question ID into the text box labeled "Question", and then click "Browse..." to select the desired file to upload. The question ID is the eight-digit number after the "Q_" in the question URL. Type a comment describing the file. Click "Upload" to upload the file. A page will then be presented with a URL that can be posted in the Experts-Exchange question so that others can download the file.

There are other free file sharing services available:

Geocities: http://geocities.yahoo.com/ps/learn2/HowItWorks4_Free.html
AngelFire: http://www.angelfire.lycos.com/doc/subscriptions/index.html
Google: http://base.google.com/base/default
RapidUpload: http://www.rapidupload.com/

If you do not want to use any of these services you can send your file to me using the email address in my profile (http://www.experts-exchange.com/M_1677072.html) and I will post it on my own site.

Kevin
0
 
xaniusCommented:
The first is text, the other is numbers :-)

I presume the developers of excel have considered the possibility of misinterpretation not fully displayed numbers as too error prone to allow for it; e.g.

"-1,000" could look like "1,000" if the cell isjust a little too small,

whereas the possibility of misinterpreting text is much smaller.

Cheers
Xanius
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
bonbonbon_jazzAuthor Commented:
Thanks to both of you!  It is a word in cell A1 and a number in cell A2.  However, even when I format the number in A2 as "text", it still gives me ###.  I have uploaded an example at: https://filedb.experts-exchange.com/incoming/ee-stuff/3010-Example.zip 
0
 
bonbonbon_jazzAuthor Commented:
I've just noticed that if I put a ' in front of the number, it will overlap.  Do I have to do this to all the numbers, even if they're formatted as "text"?  Is this normal?
0
 
byundtCommented:
Hello bonbonbon_jazz,
Yes, you will need to put a single quote in front of every number. Merely changing the format does not change what is stored in the cell--only how it is displayed. Changing the format will affect how future information that is entered into the cell is stored, however. So if you format column A as text, any future numbers you enter into that column will overlap as you intend.

To convert any existing numbers into text:
1) Select your data in column A
2) Open the Data...Text to Column menu item
3) Choose "Delimited" in the first step of the resulting wizard. Click "Next"
4) Uncheck all the options in the second step of the wizard. Click "Next"
5) Choose the option for Text in the third step of the wizard, then click "Finish"


Regards,

Brad
0
 
byundtCommented:
bonbonbon_jazz,
Since my comment built on xanius' observation that numbers do behave differently from text, I think that the points should have been split in this case. I am hoping that you will agree, and (acting in my capacity as Zone Advisor) have reopened the question.

The links to split the points are located in each Comment, and labelled "Accept Multiple Solutions".
Brad
0
 
bonbonbon_jazzAuthor Commented:
OK, I've split up the points per your suggestion.  Thanks again!
0
 
zorvek (Kevin Jones)ConsultantCommented:
>Kevin, I think you're wrong this time
I was absolutely wrong. I read "Now if I add a number to cell A2" as "Now if I add the same text to cell A2".

I don't deserve any points for this. If anything I should have points taken away!

Kevin
0
 
byundtCommented:
bonbonbon_jazz,
Thanks for being so understanding about splitting the points.

I post in a lot of questions, often times after other experts have already asked the key questions to determine the underlying problem. They would be justifiably riled if I made a habit of snagging the points at the last moment, so a split is absolutely the right way to handle the matter.

Brad
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.