Forum Discussion

AdiAtNoname's avatar
AdiAtNoname
Copper Contributor
Jan 15, 2020

CONCAT Function Not Working

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 t...
Excel
Formulas and Functions
ChrisMendoza's avatar
ChrisMendoza
Iron Contributor

tencat951- take a look at HansVogelaar response

HansVogelaar 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.

 

Jean_G-10's avatar
Jean_G-10
Copper Contributor
Oct 20, 2022

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)  
  • ChrisMendoza's avatar
    ChrisMendoza
    Iron Contributor
    Oct 20, 2022

    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.

    • Jean_G-10's avatar
      Jean_G-10
      Copper Contributor
      Oct 21, 2022

      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.

       

       

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Oct 20, 2022

    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.

Resources