Excel Magic Trick # 166: CONCATENATE function & Ampersand

Advantages and disadvantages of the Ampersand symbol and the CONCATENATE function (both join two (2) or more elements together into one text string). Also, see the DOLLAR and TEXT functions.
The Ampersand symbol and the CONCATENATE function both join 2 or more elements together into one text string.
When using the Ampersand (&), you must remember to out Text in Quotes (Double Quotes) and to add an & every time you join something or add something to the text string
The DOLLAR function takes a number and formats it with Currency Number format and converts the number to text.
The TEXT function converts a number to text and applies a number format that you specify

Category:

Science & Technology

Tags:

• Excel
• Ampersand
• CONCATENATE
• join
• two
• or
• more
• text
• string
• Mike
• Gel
• Girvin
• Highline
• Community
• College

[Markblackburn86]

Again, thanks this is very helpfull. just a quick question: What is the formula for the "date" or "Now"? Whenever a Formula date or time the string it converts it back to the numeric enrty i.e. 42265. for example ="the date is "&A1 . A1 is =Today() or =Now().

[ExcelIsFun]

Try:

="the date is "&TEXT(A1,"mm/dd/yy")

[Markblackburn86]

Again, thanks this is very helpfull. just a quick question: What is the formula for the "date" or "Now"? Whenever a Formula date or time the string it converts it back to the numeric enrty i.e. 42265. for example ="the date is "&A1 . A1 is =Today() or =Now().

[ExcelIsFun]

I will send an e-mail with a formula that will work (from Mr Excel Message Board).

[ExcelIsFun]

it is too long for the comment section. I will send by e-mail.

[ExcelIsFun]

From the Mr Excel MB, here is one that might work:

[ExcelIsFun]

It is not a pity! It is Excel-awesome! I do not know most things in Excel so when I encounter something I do not know - it's like getting a birthday present! It means that when I figure it out or ask someone who knows, I get a new Excel nugget of knowledge!

Ill get back to you soon with a solution!

[Mynotoar]

The Maestro himself is unsure :|. That's a pity. Thanks for trying :). If I have the time or the inclination I'll pop down there some time. But if you actually knew me, you'd know that the inclination is a hard thing to come by ...

[ExcelIsFun]

That is hard one. After a few trys at a formula, I posted a question to the Mr Excel Message Board (Smartest Excelers in the World hang out at that site). The subject line for the post is:

"You Are Not Alone into YANA"

[Mynotoar]

(Wasn't sure where to post this)

I'm having trouble with concatenating certain sentences, for example, I want to use the MID function or LEFT and RIGHT in an array to get four letters, Y, A, N and A, out of "You Are Not Alone". I used ampersands to join the MID formulas, then it only gave me the Y, when I nested the second MID formula inside the first.

Any idea what I'm doing wrong? O_o.