• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Concatenate function

M

marcvanderpeet12

Member
Dear all,


I have a small problem. I want to use the concatenate function to merge a word (average) and a sum of a column. I works fine, only thing is that it show 8 digits behind the comma (and I can't adjust that using format cells.


Anybody any help on this?


Dear Regards,


Marc
 
Good morning marcvanderpeet12


To save a few keyboard presses and one or two mouse clicks you could use....=(A1&" "&B1)
 
Marc - bobhc's suggestion is a bit quicker than CONCATENATE but I believe you'll still need to use the TEXT function to get your number into the desired format.


- juanito
 
Good morning juanito

I ran the formula with text and number and it concatenated without any trouble, for some reason the word concatenate reminds me of Spanish flamenco dancers, my mind is in another world :)
 
Hi bobhc - not sure if it applies to you personally, but given how many participants on Chandoo are from the Indian subcontinent it's probably worth pointing out that Spanish flamenco music is directly descended from Indian music via the Gypsies!


If you don't use TEXT like in your direct join, you will pick up any extra decimals hidden away behind the number format. So for example if you have 2.01 displayed as 2, using "&" without TEXT will give you 2.01


- Juanito olé
 
Hi, marcvanderpeet12!

Why not formatting the related cell as this?

"Average " #,##0.00

It'll still be a number and displayed as the concatenation you wanted. Adjust the numeric part of the custom format as required.

Regards!
 
Good evening juanito


Opps. Too busy tracing my lineage to find out how far my Celtic/Gaelic blood goes :)
 
@b(ut)ob(ut)hc

Hi!

I'm sure you're the grand son of Danu...

Regards!

PS: Or the brother? Nah, he isn't such old...
 
@Muneer

It's not a formula, it's a cell format. Right-click on cell, format, go to the number tab. Select custom, and in the box input

"Average " #,##0.00


This will cause the word Average to appear in the cell and the number to be formatted with 2 zeros, while keeping the actual value of the cell the same (aka, a number).
 
@nazmul_muneer

Hi!

Give a look at this file:

https://dl.dropbox.com/u/60558749/Concatenate%20function%20%28for%20marcvanderpeet12%20at%20chandoo.org%29.xlsx

and check format of cell B6.

Regards!
 
You must log in or register to reply here.
Back
Top