Hi Excellers. Today it is time for some more formula fun. Here is one way to display percentages and numbers together in one column that are currently stored in separate columns on a worksheet, it’s a neat little trick I used recently when I had to display both a numeric value as well as it’s corresponding percentage value in the same text box on a diagram in Excel. We use the concatenate formula or function to do this.
All I needed to do was join or ‘concatenate’ the two values into one column then reference the text box to that column cell. The result was a nice, neat annotated diagram.
Do you want to learn the basics about Concatenate?.
The syntax of this formula is
Up to 255 text entries can be added to the function and each one of them should be separated by a comma. One point to note, CONCATENATE does NOT add in extra spaces between text, in order to do this, you need to accommodate these extra spaces within the formula.
Or you can check out my Blog Post right HERE!
If you want to jump right in and look at joining together a number and a percentage then the syntax of the formula looks something like this:-
See it in action in my example below
Give this one a go- let me know how you get on. How easy was that?. Don’t be put off by this terminology, the concatenation of cells is one handy little Excel feature that is fabulous. Basically, it means combining the values of two cells. The great thing about Excel is that it has functions that work with arithmetic functions but it also ones that work well with text as we have seen.
Want To Become Even More Awesome In Excel?….Then Check Out My Recommended Resources
Thank You for your inform and i have been succed used this formula.
but in my case, for % is 0.11%… if i combine it, percentage just show 0%
please help me to solve this.
Change the “0%” to “0.00%” it will keep 2 decimal places…
kushal shah says
how to use minus(-) in concatenate formula
Thank You this was really helpful !
Glad you found it helpful
Thanks! This is an awesome workaround that saves me at least 45m a month.
Happy it helps Patrick
Why not use & ? It’s much simpler than =concatenate