Combining data columns - Excel changes number

Hello,

I am having some problems when I combine two columns of data.

My first column of data is prices : $39.95
My second column is % off : 50%

What I am trying to do is combine the two columns of data so they would read :
$39.95 You save 50%

I am using a formula as follows =C2&"You Save"&D2

When I use this formula the output is 39.5You Save0.5

Now the no spaces between You Save is one thing, I could probably use a find & replace on that and put in some spaces. What is driving me up a damn wall, however, is the removal of $ and changing my % to a decimal. I've tried changing the new column to every column format in the options and nothing changes. Except if I set the column to "text" then the formula stops working.

What's the deal??

or try this

=TEXT(C2&" you save" &" " &D2,"text")

format the column d to text

steve

Originally Posted by stevekirk

or try this

=TEXT(C2&" you save" &" " &D2,"text")

format the column d to text

steve

When I format column D to text, it immediately changes my % values to decimal and removes the % sign. Ie.. 50% now = .5 -- This being said even if I complete the formula the $ sign is still removed from my prices ;( so it shows 39.95 you save .5 which brings me back to square 1

Using the TEXT function allows you to specify the format for number conversion.
This formula should do what you want.
=TEXT(C2"$#,##0.00")&" You Save "&TEXT(D2,"00%")

This as soon as I put it in just says it has an error but doesn't give me any specifics as to what it is.


Either way thanks for trying ;=)

hi, cannot undersatnd why

see attached
steve

Originally Posted by mikerickson

I missed a comma

=TEXT(C2 , "$#,##0.00")&" You Save "&TEXT(D2,"00%")

That got it.

Thank you both, just made my night