View Question
 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```
 ```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```
 ```or just ="24"&"-"&"001"```
 ```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"))```