Solved

Overlapping Text vs. ### in Excel

Posted on 2007-03-28
10
3,684 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 80

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 80

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 80

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

760 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

20 Experts available now in Live!

Get 1:1 Help Now