CONCAT Function Not Working

[AdiAtNoname]

Hi,

Using Office 365 here (installed on PC).

Entering =CONCAT(G2," ",H2) to join the two text values in these cells and when clicking enter the formula appears in the cell, not a string of the two words in the cells I want to combine.

Also tried =G2&," ",&H2, and showing formula in cell.

Anyone else experiencing this?

Thank you.

[#2]

@AdiAtNoname -

Not sure what you're experiencing on your side but this is what I get:

You don't need the commas when using & to join.

[#3]

@AdiAtNoname 

if you see formula instead of the result please be sure this setting is not checked

[#4]

@AdiAtNoname Same problem here using Excel 2016. I tried to combine two columns of data into one using the =A2&" "&B2 formula example, and also tried =concat(A2," ",B2) and neither are working. When I hit enter all it shows is the formula I entered. I have also check to make sure the Show Formulas button is not selected. 

[#5]

@Lauraleiwhit 

Another variant if your cell is formatted as text. Check by Ctrl+1.

[#6]

@Sergei Baklan All data in both columns are formatted as text. But here's what just happened. I tried the formula in a blank worksheet and it worked. Then when I went back to my data worksheet to try again, it finally worked. I entered the same formula again and viola, it worked. I don't know why this was the case. I had originally typed in the cell references instead of selecting them by clicking on them. Is this possibly the trick??
Regardless, my problem is solved.
This might help someone else though??

[#7]

@Lauraleiwhit 

The problem is not with the referenced cells being formatted as text; it is likely that the formula cell was accidentally formatted as text which will block any formula you may enter into that cell from working.

[#8]

@Peter Bartholomew I did check that when I was trying to troubleshoot and I believe it was set as General. So how should the formula cell be formatted? 

[#9]

@Lauraleiwhit 

General should be fine.  There is a catch in that reinstating General in place of Text doesn't automatically  sort the problem.  The formula also has to be committed with Enter as if it were newly input.

[#10]

This worked for me, thank you!

[#11]

@Peter Bartholomew THANK YOU. This is exactly the problem I was having and I can't believe it took more than an hour to finally have someone mention this may be the issue. You sir, are a savior.

[#12]

@ChrisMendoza 

I have typed in the formula just as shown - I tried this using ampersand and the concat function. And the result is not the function but the formula I typed in.

I successfully completed this task on another work sheet in the same document. Only difference is I was combining two text cells and not three. 

Very frustrating.

Help

[#13]

@tencat951 

As mentioned in other replies in this discussion:

• Make sure that the cell with the formula is not formatted as Text.
• Make sure that the Show Formulas button in the Formula Auditing group of the Formulas tab of the ribbon is not highlighted.

[#14]

@tencat951- take a look at @Hans Vogelaar response

---

@Hans Vogelaar wrote:

As mentioned in other replies in this discussion:

• Make sure that the cell with the formula is not formatted as Text.
• Make sure that the Show Formulas button in the Formula Auditing group of the Formulas tab of the ribbon is not highlighted.

---

[#15]

@ChrisMendoza Hi there. I have copied a Concat formula into a weekly spreadsheet for over a year and it worked like charm. Someone else created it, I'm not expert. But all of a sudden it won't calculate any more.  It is formatted as "General' and "Show Formulas" under the Formulas menu is off. I have tried to copy the formula into a different brand new Excel file and it still doesn't work.  Help!

=CONCATENATE("[",LEFT(D2,1),LEFT(MID(D2,(FIND(" ",D2,1))+1,256),1),"-",C2,"]"," ",F2)  

[#16]

@Jean_G-10 - are you getting a #VALUE error? I would suggest you 'Evaluate Formula' in the Formulas Ribbon to find where the function fails. As an example, not having a space in the text string during the LEFT(MID(... part will cause a failure on your FIND( ...

Other than that, you'll need to provide a sample of the failing text string and one that is working correctly.

[#17]

@Jean_G-10 

I used LET to examine the formula.  Basically, it should work as you have it provided the data is correct.  Since I haven't the remotes idea what your cell references are returning, I replaced them with similarly uninformative defined names.

= LET(
      firstSpace,    IFERROR(FIND(" ", columnD), 0),
      firstInital,   LEFT(columnD, 1),
      secondInitial, IF(firstSpace, MID(columnD, firstSpace+1, 1), "?"),
      CONCATENATE("[",firstInital,secondInitial,"-",columnC,"]"," ",columnF)
   )

The formula calculates a complete column of results as a dynamic array and returns "?" if it cannot find the space.

[#18]

@ChrisMendoza 

Thanks for your quick reply

I am not getting a #VALUE error, or any kind of error.

I did "Evaluate Formula" on the one that does NOT calculate/work, and tried to upload the image but it won't let me.  The popup says "The cell currently being evaluated contains a constant.

Here is the formula that does NOT work, copied from the cell

=CONCATENATE("[",LEFT(D2,1),LEFT(MID(D2,(FIND(" ",D2,1))+1,256),1),"-",C2,"]"," ",F2)

OR COPIED from the Formula Bar

=CONCATENATE("[",LEFT(D2,1),LEFT(MID(D2,(FIND(" ",D2,1))+1,256),1),"-",C2,"]"," ",F2)

Here is a Concat formula that IS working. 

=CONCATENATE("[",LEFT(D2,1),LEFT(MID(D2,(FIND(" ",D2,1))+1,256),1),"-",C2,"]"," ",F2)

I did Evaluate formula here too and the message reads:

"To show the result of the underlined expression, click Evaluate.  The most recent result appears italicized." I clicked Evaluate several times and it cycles through the data desired each time I click Evaluate. First, the underlined expression is the first appearance of "D2" above.  The field contains employee name. When I click Evaluate the person's name pops into the formula instead of the first appears of D2. I simulated this below.

=CONCATENATE("[";LEFT("First Last';1);LEFT(MID(D2;(FIND(" ";D2;1))+1;256);1);"-";C2;"]";" ";F2)

This is so weird - can you figure out what's wrong??

Thank you,

Jean G.