Google Answers Logo
View Question
 
Q: Microsoft Excel number formatting question ( Answered,   2 Comments )
Question  
Subject: Microsoft Excel number formatting question
Category: Computers > Software
Asked by: switters1-ga
List Price: $10.00
Posted: 07 Aug 2006 21:21 PDT
Expires: 06 Sep 2006 21:21 PDT
Question ID: 753678
In Microsoft excel, how would I set up a custom cell format such that
a concatenation that reads like this:

24&"-"&001 

will actually display as 24-001 and not as 24-1
Answer  
Subject: Re: Microsoft Excel number formatting question
Answered By: tisme-ga on 07 Aug 2006 21:28 PDT
 
Hello switters1-ga,

You need to ensure that the resulting cell column is formated to type
TEXT or GENERAL first. (Highlight the column, right click it, Format
Cells, and change the type to TEXT).

Your formula will be: =CONCATENATE("24", "-", "001")
If the 24 and the 001 originate from a cells change this to their
respective cells: =CONCATENATE(A1, "-", B1)

In the above case, make sure that B1 is type text as well to get 001 instead of 1. 

Let me know if you need any clarification,

tisme-ga

Request for Answer Clarification by switters1-ga on 08 Aug 2006 11:12 PDT
To clarify,

assume the 24 and the 1 are values returned by an equation. I do not
have the option of just typing them in as text. So to recap, here are
two cells (A1 and A2):

24
1

Both values are returned by some equation. Now the cell that contains
the 1 could very well be formatted to display numbers as 001, however
when the following concatenation occurs:

=A1&"-"&A2

The resultant cell reads 24-1. I need somebody to present me with a
custom format for this resultant cell that would display the incoming
numbers as 24-001. I hope this is more clear. Thank you.

Clarification of Answer by tisme-ga on 08 Aug 2006 11:16 PDT
Question for you: 

Is the second value ever above the number 9? 
If yes, is the highest number possible 999? 

I think we can do this by converting the output into text (in a
different column) and then bringing it together at that point.

tisme-ga

Request for Answer Clarification by switters1-ga on 08 Aug 2006 12:23 PDT
Converting to text in another column was the first thing I tried, but
text will still just display the 1 alone.

Even if I did somehow "hack it" and get the text to display then copy
into the cell I'm interested it, I am still looking for a custom
format string, eg, something along the lines of ##\-000 or however
excel handles these things.(I don't have the time to learn the
intricacies of custom formats right now)

Clarification of Answer by tisme-ga on 08 Aug 2006 12:43 PDT
Hello switters1-ga,

I think I have what you are looking for now: 

=CONCATENATE(H1,"-",IF(I1<10,"00"&I1,"0"&I1))

Here, I assume the first number is in H1 and the number after the dash is in I1. 

Change to your values where indicated below:
=CONCATENATE(H1,"-",IF(I1<10,"00"&I1,"0"&I1))
             ^^        ^^         ^^     ^^
             ||        ||         ||     ||

All the best,

tisme-ga

Clarification of Answer by tisme-ga on 08 Aug 2006 15:25 PDT
sophiephie-ga has posted another working formula that you might prefer: 

===========================================================
Subject: Re: Microsoft Excel number formatting question
From: sophiephie-ga on 08 Aug 2006 12:51 PDT 	 

Here is a simple and elegant formula that will work:
A1 is the number 24
B1 is the number 1, 22, or 333, or even 4444

to make 24-001, use functions CONCATENATE and TEXT
=CONCATENATE(A1, "-", TEXT(B1, "000"))
===========================================================

Request for Answer Clarification by switters1-ga on 08 Aug 2006 22:32 PDT
What sophiephie-ga posted is exactly what I was looking for: an
elegant, one-line, unconditional solution (ie, no 'if's). Although
tisme-ga posted the "hack" solution,

Change to your values where indicated below:
=CONCATENATE(H1,"-",IF(I1<10,"00"&I1,"0"&I1))
             ^^        ^^         ^^     ^^
             ||        ||         ||     ||

it does not cover all cases, ie, any possible number between 0 and
999. Granted, an additional nested if clause will make it look right,
I feel sophiephie-ga hit the nail on the head.

Clarification of Answer by tisme-ga on 08 Aug 2006 22:39 PDT
Hello switters1-ga,

I am glad we were able to help you get this resolved. I agree that
sophie's code is much more elegant than my own. :)

All the best,

tisme-ga
Comments  
Subject: Re: Microsoft Excel number formatting question
From: reinedd-ga on 08 Aug 2006 05:06 PDT
 
or just
="24"&"-"&"001"
Subject: Re: Microsoft Excel number formatting question
From: sophiephie-ga on 08 Aug 2006 12:51 PDT
 
Here is a simple and elegant formula that will work:
A1 is the number 24
B1 is the number 1, 22, or 333, or even 4444

to make 24-001, use functions CONCATENATE and TEXT
=CONCATENATE(A1, "-", TEXT(B1, "000"))

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy