Solved

Overlapping Text vs. ### in Excel

Posted on 2007-03-28
10
3,704 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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
 
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

770 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