I have what is essentially a simple formatting question that involves
combining a TEXT function within a sentence. Before I get to my
question I ought to provide some context.

I built a table that uses VLOOKUP to list the number of cases sold by
month.Then I need to show the net difference in cases sold between the
current month and the same month a year ago. The table works fine. My
problem lies in building a sentence that states this difference. The
heart of the problem is that forces beyond my control insist that the
difference in the number of cases be expressed as XXXk rather than the
whole number XXX,XXX.

Here's an example.
The date "Jan 2010" is in cell N2 (As an aside, the all-knowing powers
that be insist the date be formatted as 2010/01)
The date "Jan 2009" is in cell B2
The number of Jan 2010 case sales are in N3; the amount is 6,053,021
The number of Jan 2009 case sales are in cell B3; the amount is
4,840,747
The difference is 1,212,274 (did I tell you I was a math major?)
(Between columns B and N are the number of case sales for Feb thru
Nov)

To summarize the report I need a sentence string that reads: 2009/01
versus 2010/01 sales is 1,212k cases

Here's the formula I created for this sentence:
=TEXT(B2," "yyyy/mm")&" versus "&TEXT(N2,"yyyy/mm")&" sales is
"&TEXT(ROUNDDOWN(N3-B3)/1000,"#,#")&"k cases"

So after all this set-up, here's my goofy little problem. There's a
space that I can't get rid of between the difference in the number of
cases and the "k" ,which I need to have snugly fit against it. I have
made sure that there is no space sneaking when I type "k cases"

Now I'm an easy-going guy who could let this slide but the individual
in charge of this project finds it unacceptable. Any help is
appreciated.

"#,#k")&" cases"


Gord Dibben MS Excel MVP


On Mon, 15 Feb 2010 14:21:12 -0800 (PST), Evan
wrote:

I have what is essentially a simple formatting question that involves
combining a TEXT function within a sentence. Before I get to my
question I ought to provide some context.

I built a table that uses VLOOKUP to list the number of cases sold by
month.Then I need to show the net difference in cases sold between the
current month and the same month a year ago. The table works fine. My
problem lies in building a sentence that states this difference. The
heart of the problem is that forces beyond my control insist that the
difference in the number of cases be expressed as XXXk rather than the
whole number XXX,XXX.

Here's an example.
The date "Jan 2010" is in cell N2 (As an aside, the all-knowing powers
that be insist the date be formatted as 2010/01)
The date "Jan 2009" is in cell B2
The number of Jan 2010 case sales are in N3; the amount is 6,053,021
The number of Jan 2009 case sales are in cell B3; the amount is
4,840,747
The difference is 1,212,274 (did I tell you I was a math major?)
(Between columns B and N are the number of case sales for Feb thru
Nov)

To summarize the report I need a sentence string that reads: 2009/01
versus 2010/01 sales is 1,212k cases

Here's the formula I created for this sentence:
=TEXT(B2," "yyyy/mm")&" versus "&TEXT(N2,"yyyy/mm")&" sales is
"&TEXT(ROUNDDOWN(N3-B3)/1000,"#,#")&"k cases"

So after all this set-up, here's my goofy little problem. There's a
space that I can't get rid of between the difference in the number of
cases and the "k" ,which I need to have snugly fit against it. I have
made sure that there is no space sneaking when I type "k cases"

Now I'm an easy-going guy who could let this slide but the individual
in charge of this project finds it unacceptable. Any help is
appreciated.

In addition to what Gord Dibben provided, shouldn't the
ROUNDDOWN(N3-B3)/1000,
portion be
ROUNDDOWN(N3-B3,1)/1000,
?
From Excel 2003 Help for ROUNDDOWN():
Syntax

ROUNDDOWN(number,num_digits)

Number is any real number that you want rounded down.

Num_digits is the number of digits to which you want to round number.


"Evan" wrote:

I have what is essentially a simple formatting question that involves
combining a TEXT function within a sentence. Before I get to my
question I ought to provide some context.

I built a table that uses VLOOKUP to list the number of cases sold by
month.Then I need to show the net difference in cases sold between the
current month and the same month a year ago. The table works fine. My
problem lies in building a sentence that states this difference. The
heart of the problem is that forces beyond my control insist that the
difference in the number of cases be expressed as XXXk rather than the
whole number XXX,XXX.

Here's an example.
The date "Jan 2010" is in cell N2 (As an aside, the all-knowing powers
that be insist the date be formatted as 2010/01)
The date "Jan 2009" is in cell B2
The number of Jan 2010 case sales are in N3; the amount is 6,053,021
The number of Jan 2009 case sales are in cell B3; the amount is
4,840,747
The difference is 1,212,274 (did I tell you I was a math major?)
(Between columns B and N are the number of case sales for Feb thru
Nov)

To summarize the report I need a sentence string that reads: 2009/01
versus 2010/01 sales is 1,212k cases

Here's the formula I created for this sentence:
=TEXT(B2," "yyyy/mm")&" versus "&TEXT(N2,"yyyy/mm")&" sales is
"&TEXT(ROUNDDOWN(N3-B3)/1000,"#,#")&"k cases"

So after all this set-up, here's my goofy little problem. There's a
space that I can't get rid of between the difference in the number of
cases and the "k" ,which I need to have snugly fit against it. I have
made sure that there is no space sneaking when I type "k cases"

Now I'm an easy-going guy who could let this slide but the individual
in charge of this project finds it unacceptable. Any help is
appreciated.
.