Use a custom formatted value within a formula

[spaich711]

I have a long CONCAT formula that pulls data from tons of different cells in a pivot table, some of which are currency values. I would like the values displayed as $X.XK. The values can go up into the hundreds of thousands of dollars. I know the formula $#,##0.0,"K" works in custom formatting if that is the only value in the cell, but I need it to be added to a formula that includes strings and VLOOKUPs. What's the easiest way to make the CONCAT formula display the text as shown in my example document? I can do the VLOOKUP that pulls the dollar amount in a cell by itself with the conditional formatting, but how do I get it to look exactly how it appears with the custom formatting in another cell?

[TMS]

You are showing a formatted text value. You CANNOT format part of a formula, regardless of how it is constructed. VLOOKUP could return a numeric value, in it's own right, and be formatted. But CONCATENATE will produce a text output whatever the component elements may be. And that can't be formatted.

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.