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
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)  
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