Solved

Overlapping Text vs. ### in Excel

Posted on 2007-03-28
10
3,693 Views
Last Modified: 2012-05-05
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!
0
Comment
Question by:bonbonbon_jazz
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 100 total points
ID: 18809698
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
 
LVL 10

Expert Comment

by:xanius
ID: 18809722
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
 
LVL 10

Accepted Solution

by:
xanius earned 200 total points
ID: 18809762
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
 

Author Comment

by:bonbonbon_jazz
ID: 18809797
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
 

Author Comment

by:bonbonbon_jazz
ID: 18809822
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 81

Assisted Solution

by:byundt
byundt earned 200 total points
ID: 18810022
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
 
LVL 81

Expert Comment

by:byundt
ID: 18810382
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
 

Author Comment

by:bonbonbon_jazz
ID: 18810964
OK, I've split up the points per your suggestion.  Thanks again!
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 18810996
>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
 
LVL 81

Expert Comment

by:byundt
ID: 18812693
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now