Concatenate and preserve number format

[adrienne0914]

Cell A1 contains $26,501,370.27. I have a custom format so that it appears as $26.501 million. Trying to figure out how to preserve this format in CONCATENATE as text. I tried TEXT(A1,$#.000,," million") but get a formula error message.

 

[MARK858]

Does A1 actually contain the number 26501370.27 rather than the text $26,501,370.27?
If it is a number then try

Book1
	A	B
1	26501370.27	$26.501 Million
Sheet1

Cell Formulas
Range	Formula
B1	B1	=TEXT(A1,"$#,###,.###,")&" Million"

 

[adrienne0914]

Thanks so much, MARK858! I never would have figured that out!!

 

[MARK858]

You're welcome (please note you might need to Rounddown A1 depending on the exact results you want)

Book1
	A	B
1	26501370.27	$26.501 Million
2	26501670.27	$26.502 Million
3	626501670.27	$626.502 Million
4	26501370.27	$26.501 Million
5	26501670.27	$26.501 Million
6	626501670.27	$626.501 Million
Sheet3

Cell Formulas
Range	Formula
B1:B3	B1	=TEXT(A1,"$#,###,.###,")&" Million"
B4:B6	B4	=TEXT(ROUNDDOWN(A4,-3),"$#,###,.###,")&" Million"

 

[adrienne0914]

No, your solution worked perfect. No revisions necessary, but I will save this for future reference just in case.

 

[Peter_SSs]

No, your solution worked perfect.

It seemed to me from post 1 that your custom format and your desired concatenated output would have 3 decimal places. The suggested formula does not always do that - see B2:B4 below.

If my interpretation was correct then perhaps the column C formula might be more what you want?
.. and column D was just for fun to show that it can be done within the text function rather than with "&" if you want (though I am not recommending that )

24 10 15.xlsm
	A	B	C	D
1	26501370.27	$26.501 Million	$26.501 Million	$26.501 Million
2	26000000	$26. Million	$26.000 Million	$26.000 Million
3	26510370.27	$26.51 Million	$26.510 Million	$26.510 Million
4	26500370.27	$26.5 Million	$26.500 Million	$26.500 Million
5	26501870.27	$26.502 Million	$26.502 Million	$26.502 Million
Millions

Cell Formulas
Range	Formula
B1:B5	B1	=TEXT(A1,"$#,###,.###,")&" Million"
C1:C5	C1	=TEXT(A1,"$#,##0.000,,")&" Million"
D1:D5	D1	=TEXT(A1,"$#,##0.000,, \Millio\n")

 

[adrienne0914]

It seemed to me from post 1 that your custom format and your desired concatenated output would have 3 decimal places. The suggested formula does not always do that - see B2:B4 below.

If my interpretation was correct then perhaps the column C formula might be more what you want?
.. and column D was just for fun to show that it can be done within the text function rather than with "&" if you want (though I am not recommending that )

Peter_SSs, I actually didn't have any issues with the original solution. The result was consistently 3 decimal places. But I have noted yours for future reference. Much appreciated!

 

[Peter_SSs]

Peter_SSs, I actually didn't have any issues with the original solution. The result was consistently 3 decimal places.

I don't know how your data is arrived at but I would say that is more good luck than that the original formula is robust.

If any of your data could ever have zeros in

• the thousands column, or
• the thousands and tens of thousands columns, or
• the thousands and tens of thousands and hundreds of thousands columns

then the given formula will not return 3 decimal places.
Are you saying that the sample data that I have in column A above is impossible for you?

 

[adrienne0914]

Are you saying that the sample data that I have in column A above is impossible for you?

Here are the numbers that I used in my CONCATENATE formula:

$26,501,000
$1,413,000
$2,192,000
$9,641,000
$13,624,000
$848,000
$1,368,000

I also rounded to the nearest thousand. And it just so happened that there were no zeroes at the end of the thousands column. I'm sure it won't always be that way. That's why I am saving your solution for the future. Thanks again.

 

[MARK858]

Personally I would just change to the version Peter proposed in cell C1, there are no downsides to using it