How to Make CONCATENATE use the displayed value of the cell instead of the actual (PIC)

[bssol]

Hi guys.

I run to this problem when using CONCATENATE function. I want to merge some cells who were formatted to show the number a little different from the actual value. for example, to show 33M instead of 33000000

Now when I use CONCATENATE, it will show that cell as 33000000 and I got stock in there. Please see the attached picture

now, is there any way around to solve this problem? maybe some function here or there!, Open for all suggestions


Thank you

[Andrew-R]

Try this formula in cell D3 and copy down:

=A3 & TEXT(B3,"0%") & " (" & TEXT(C3,"[>=1000000]0,,""M"";[>=1000]0,""K"";0") & ")"

[FDibbins]

try this...
A4&" "&TEXT(MROUND(B4,0.01),"#%")&" "&TEXT(ROUND(C4/1000000,7),"##")&"M"

[oeldere]

Maybe like this (see the yellow cell in G3)

[bssol]

Try this formula in cell D3 and copy down:

=A3 & TEXT(B3,"0%") & " (" & TEXT(C3,"[>=1000000]0,,""M"";[>=1000]0,""K"";0") & ")"

Worked like a charm!

Thank you all guys. now I'm no longer stocked!