Home

Concat function not woking in Excel version 1903

%3CLINGO-SUB%20id%3D%22lingo-sub-394266%22%20slang%3D%22en-US%22%3EConcat%20function%20not%20woking%20in%20Excel%20version%201903%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394266%22%20slang%3D%22en-US%22%3E%3CP%3EI%20just%20installed%20Excel%20version%201903%20(had%20previously%20been%20on%201901%20or%201902)%20and%20I%20came%20across%20%22_xlfn.%22%20preceding%20my%20CONCAT%20functions%20in%20a%20worksheet.%26nbsp%3B%20I%20use%20the%20thick%20client%20(Professional)%20but%20am%20an%20Office%20365%20subscriber.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFormula%20now%20shows%20as%3A%3C%2FP%3E%3CP%3E%3D_xlfn.CONCAT(TEXT(Week2StartDate%2C%22MMM-DD%22)%2C%22%20-%20%22%2CTEXT(Week2EndDate%2C%20%22MMM-DD%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOriginally%20was%3A%3C%2FP%3E%3CP%3E%3DCONCAT(TEXT(Week2StartDate%2C%22MMM-DD%22)%2C%22%20-%20%22%2CTEXT(Week2EndDate%2C%20%22MMM-DD%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERemoving%20the%20%22_xlfn.%22%20throws%20a%20%22%23NAME%3F%22%20error%20as%20does%20just%20clicking%20enter%20in%20the%20cell%20without%20making%20any%20changes%2C%20but%20replacing%20with%20CONCATENATE%20works.%26nbsp%3B%20The%20original%20works%20just%20fine%20in%20the%20web%20view.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%20is%20CONCAT%20not%20working%20in%20the%20latest%20version%20of%20Excel%3F%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-394266%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-407812%22%20slang%3D%22en-US%22%3ERe%3A%20Concat%20function%20not%20woking%20in%20Excel%20version%201903%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-407812%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F106118iB5A764FD858B359F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-401341%22%20slang%3D%22en-US%22%3ERe%3A%20Concat%20function%20not%20woking%20in%20Excel%20version%201903%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-401341%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313753%22%20target%3D%22_blank%22%3E%40rdwyer%3C%2FA%3E%26nbsp%3B%2C%20may%20you%20share%20info%20about%20your%20Excel%20version%20as%20screenshort%20like%20here%3F%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20395px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F106021iED0A452622DC516A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394315%22%20slang%3D%22en-US%22%3ERe%3A%20Concat%20function%20not%20woking%20in%20Excel%20version%201903%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394315%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3EMy%20install%20was%20done%20from%20the%20cloud%2C%20as%20was%20my%20previous%20version.%26nbsp%3B%20It%20is%20the%20365%20thick%20client.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-394279%22%20slang%3D%22en-US%22%3ERe%3A%20Concat%20function%20not%20woking%20in%20Excel%20version%201903%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394279%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313753%22%20target%3D%22_blank%22%3E%40rdwyer%3C%2FA%3E%20%2C%20_xlfn%20means%20what%20function%20is%20not%20available%20for%20your%20version%20of%20Excel.%20CONCAT%20is%20only%20for%20Excel%20which%20is%20part%20of%20Office365%20package%20(other%20words%20you%20shall%20install%20it%20from%20the%20cloud).%20For%20Office%202016%20Pro%20CONCAT%20is%20not%20available.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-408300%22%20slang%3D%22en-US%22%3ERe%3A%20Concat%20function%20not%20woking%20in%20Excel%20version%201903%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-408300%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313753%22%20target%3D%22_blank%22%3E%40rdwyer%3C%2FA%3E%26nbsp%3B%2C%20that's%20strange%2C%20the%20function%20is%20definitely%20supported%20by%20monthly%20channel%20Excel%20version.%20If%20you%20open%20attached%20file%20in%20this%20your%20version%20do%20you%20see%20the%20same%20error%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-408577%22%20slang%3D%22en-US%22%3ERe%3A%20Concat%20function%20not%20woking%20in%20Excel%20version%201903%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-408577%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bnow%20that's%20VERY%20weird%20-%20I%20do%20not%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20620px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F106130iD7FD82B2F82CB25E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-408640%22%20slang%3D%22en-US%22%3ERe%3A%20Concat%20function%20not%20woking%20in%20Excel%20version%201903%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-408640%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bchecked%20my%20original%20file%20-%20seems%20to%20be%20working%20fine%20now.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-408673%22%20slang%3D%22en-US%22%3ERe%3A%20Concat%20function%20not%20woking%20in%20Excel%20version%201903%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-408673%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313753%22%20target%3D%22_blank%22%3E%40rdwyer%3C%2FA%3E%26nbsp%3B%2C%20have%20no%20idea%20what%20it%20was.%20Anyway%2C%20so%20far%20so%20good.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-453056%22%20slang%3D%22en-US%22%3ERe%3A%20Concat%20function%20not%20woking%20in%20Excel%20version%201903%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-453056%22%20slang%3D%22en-US%22%3EHi.%20I'm%20with%20a%20problem%20like%20this.%20I'm%20running%201904%20build.%20My%20CONCAT%20is%20like%20this%3A%3CBR%20%2F%3E%3DCONCAT(A1%3AA4%26amp%3B%22%2F%22)%3CBR%20%2F%3E%3CBR%20%2F%3ESupposing%20that%20A1%3AA4%20is%20A%2C%20B%2C%20C%20and%20D%2C%20the%20result%20is%20%22A%2FB%2FC%2FD%2F%22.%20But%20this%20works%20only%20in%20Excel%20inside%20preview%20version%2C%20especifically%201904.%20Otherwise%2C%20I%20receive%20an%20error%20or%20the%20formula%20comes%20between%20%22%7B%22%20and%20%22%7D%22%20(like%20matrix%20formula)%2C%20even%20in%201903.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20don%C2%B4t%20know%20if%20this%20is%20like%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F313753%22%20target%3D%22_blank%22%3E%40rdwyer%3C%2FA%3E%20problem.%20But%20seems%20like%20it.%3CBR%20%2F%3E%3CBR%20%2F%3EPS%3A%20Will%20CONCAT%20works%20in%20Office%202019%20(or%20others)%20anytime%20in%20the%20future%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20in%20advance!%3C%2FLINGO-BODY%3E
rdwyer
Occasional Contributor

I just installed Excel version 1903 (had previously been on 1901 or 1902) and I came across "_xlfn." preceding my CONCAT functions in a worksheet.  I use the thick client (Professional) but am an Office 365 subscriber.

 

Formula now shows as:

=_xlfn.CONCAT(TEXT(Week2StartDate,"MMM-DD")," - ",TEXT(Week2EndDate, "MMM-DD"))

 

Originally was:

=CONCAT(TEXT(Week2StartDate,"MMM-DD")," - ",TEXT(Week2EndDate, "MMM-DD"))

 

Removing the "_xlfn." throws a "#NAME?" error as does just clicking enter in the cell without making any changes, but replacing with CONCATENATE works.  The original works just fine in the web view.

 

Why is CONCAT not working in the latest version of Excel??

11 Replies

@rdwyer , _xlfn means what function is not available for your version of Excel. CONCAT is only for Excel which is part of Office365 package (other words you shall install it from the cloud). For Office 2016 Pro CONCAT is not available.

@Sergei BaklanMy install was done from the cloud, as was my previous version.  It is the 365 thick client.

@rdwyer , may you share info about your Excel version as screenshort like here?

image.png

 

@rdwyer , that's strange, the function is definitely supported by monthly channel Excel version. If you open attached file in this your version do you see the same error?

@Sergei Baklan now that's VERY weird - I do not:

 

Capture.PNG

@Sergei Baklan checked my original file - seems to be working fine now.

@rdwyer , have no idea what it was. Anyway, so far so good.

Hi. I'm with a problem like this. I'm running 1904 build. My CONCAT is like this:
=CONCAT(A1:A4&"/")

Supposing that A1:A4 is A, B, C and D, the result is "A/B/C/D/". But this works only in Excel inside preview version, especifically 1904. Otherwise, I receive an error or the formula comes between "{" and "}" (like matrix formula), even in 1903.

I don´t know if this is like @rdwyer problem. But seems like it.

PS: Will CONCAT works in Office 2019 (or others) anytime in the future?

Thanks in advance!

@Marcus Vinicius , CONCAT with the delimiter works as an array formula on my 1903. And it shall be CSE formula in such case, otherwise TEXTJOIN.

@Sergei Baklan  I encountered this myself when moving to Windows 10 1809.  I get the "_xlfn." for IFS() when I double-click on the excel file from File Explorer.  But the file will load properly if I open Excel and then open the XLSX file from within Excel with the proper IFS()