• 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() or

C

Clarity

New Member
Hi,


Just as a point of interest as there have been a few posts on Concatenate recently. I tend to use "&" rather than the concatenate formula. eg:


="Hello"&" everybody"


instead of


=CONCATENATE("Hello"," everybody")


I haven't come across any issues using it and it seems to work in the same way as CONCATENATE. The only possible issue I can see is other users not being able to understand the formula.


Does anyone have any thought?


Cheers,


Myles
 
I believe '&' is a throw back to Lotus 123 which Excel has maintained for compatibility
 
CONCATENATE function is used when you want to join 2 or more characters or strings in Excel. The characters can be space(“ “), Punctuation mark(“,”), Integer (1) and special character(“#”). CONCATENATE function can be used to join text, numbers, cell references,special characters or a combination of these items. With "&" I don't think all these options will work. Even "&" is also a special character. If you want to join "&" with some other set of characters it is best to use CONCATENATE function to avoid any issue or confusion.
 
Excel documentation now shows CONCATENATE as being available for reasons of backward compatibility only. It has been replaced by CONCAT. In some ways the behaviour of CONCAT and "&" parallels that of SUM and "+". If 'text' and 'numbers' were arrays (increasingly the case for Office 365) then
= text & numbers
= CONCAT(text, numbers)
= CONCAT(text & numbers)
all give different results.

Since I only program Excel solutions using defined names, it is quite natural for me to wrap operators that may otherwise cause confusion within a defined name. For example, if 'ampersand' refers to
= " & "
then
= text & ampersand & numbers
may be considered easier to read, though requiring more keystrokes, than
= text&" & "&numbers

Odd fact: I seem to remember reading that 'ampersand' is a contraction of "and, per se, and" dating from a time when the symbol "&" was the final letter of the alphabet.
 
Last edited:
Chihiro said:
In newer versions, Office 365 / Excel 2019 specifically. Concatenate is replaced with more robust CONCAT function.
Concatenate is kept for backward compatibility.

https://support.microsoft.com/en-us...1af-9736-694cbd6b4ca2?ui=en-us&rs=en-us&ad=us

Also, TEXTJOIN was added at the same time. This allows for ignore blank and use of delimiter.
https://support.microsoft.com/en-us...9d0-80c3-0e8fc845691c?ui=en-us&rs=en-us&ad=us
Click to expand...
I particularly like TEXTJOIN as it allows for adding conditions in the evaluation part so it makes it more flexible for users. It is one of the better enhancements to come out on formula front. Until then Harlan Grove's ACONCAT was used by many.
 
shrivallabha said:
I particularly like TEXTJOIN as it allows for adding conditions in the evaluation part so it makes it more flexible for users. It is one of the better enhancements to come out on formula front. Until then Harlan Grove's ACONCAT was used by many.
Click to expand...

Besides Harlan Grove's ACONCAT, we also used MCONCAT function from the MoreFunc Add-in in the old day

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