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
|
