Formatting Parts of a Concatenated String

  • Is it possible to format part of a concatenated string?

    For instance I have the following formula


    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



    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:



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



  • 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.


    and then in the "Type" window enter:

    "Text you want " 0%


    "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!