Formatting Parts of a Concatenated String

    Is it possible to format part of a concatenated string?


    For instance I have the following formula


    =CONCATENATE(A5,G5)


    This returns the following


    Sales have decreased by -7260.39


    The -7260.39 is the value of cell G5.


    I want to be able to format this as Currency with no decimal points. However when included in the Concatenate string it returns the value as text. Is there anyway I can format it within the Concatenate string?

    alan


    Try:


    =CONCATENATE(A5," ",DOLLAR(ROUNDUP(A1,0)))


    if you want to round the cents up, change to ROUNDDOWN to round down.


    or if you want to get rid of the zeroes as well:


    =CONCATENATE(A5," $",ROUNDUP(A1,0))



    Regards
    Weasel

    Thanks a lot, I used the DOLLAR function as shown below with the 0 at the end for no decimals.


    Cheers


    =CONCATENATE(A5," ",DOLLAR(ROUNDDOWN(K5,0),0))

    Can you tell me how I can format a value as a percentage within a concatenated string?


    I have the following formula: -


    =CONCATENATE(A5," ",DOLLAR(ROUNDDOWN(K5,0),0),A6," ",K6)


    K6 is 15.59%, but when I reference it within the text string, it returns 0.1559


    Can anyone help please?

    If you want to leave the cell entry as a number so that you can use it in formulas references, you also might consider using custom formatting.


    FORMAT > CELL > NUMBER > CUSTOM


    and then in the "Type" window enter:


    "Text you want " 0%


    or


    "Text you want " 0.00%


    depending on the decimal accuracy you want displayed.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login