SOLVED

Need help on CONCAT function for multiple column values

%3CLINGO-SUB%20id%3D%22lingo-sub-3475552%22%20slang%3D%22en-US%22%3ENeed%20help%20on%20CONCAT%20function%20for%20multiple%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3475552%22%20slang%3D%22en-US%22%3E%3CTABLE%20width%3D%22385%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3ECOL1%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ECOL2%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ECOL3%3C%2FTD%3E%3CTD%20width%3D%22193%22%3ECONCAT%20RESULT%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EB972%3C%2FTD%3E%3CTD%3E02%3C%2FTD%3E%3CTD%3EDPP%3C%2FTD%3E%3CTD%3E('B972'%2C'02'%2C'DPP')%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3475552%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3475660%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20on%20CONCAT%20function%20for%20multiple%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3475660%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1416035%22%20target%3D%22_blank%22%3E%40Srikanth0990%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20formula%20for%20using%20concat%20is%20this%2C%20Concat(col1%2Ccol2%2Ccol3)%2C%20and%26nbsp%3B%3C%2FP%3E%3CP%3E%3DCONCAT(A2%2CB2%2CC2)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eif%20you%20want%20to%20add%20space%20between%20values%3A%3C%2FP%3E%3CP%3E%3DCONCAT(A2%2C%22%20%22%2CB2%2C%22%20%22%2CC2)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3477066%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20on%20CONCAT%20function%20for%20multiple%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3477066%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F87534%22%20target%3D%22_blank%22%3E%40Jihad%20Al-Jarady%3C%2FA%3E%26nbsp%3BIt's%20not%20working%20as%20expected%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3478709%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20on%20CONCAT%20function%20for%20multiple%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3478709%22%20slang%3D%22en-US%22%3EDo%20you%20want%20the%20result%20to%20be%20this%3A%20('B972'%2C'02'%2C'DPP')%3B%3CBR%20%2F%3EIf%20yes%2C%20this%20the%20formula%3CBR%20%2F%3E%3DCONCAT(%22('%22%2CA1%2C%22'%2C'%22%2CB1%2C%22'%2C'%22%2CC1%2C%22')%3B%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3478776%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20on%20CONCAT%20function%20for%20multiple%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3478776%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1416035%22%20target%3D%22_blank%22%3E%40Srikanth0990%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDepending%20on%20how%20precise%20you%20need%20the%20result%20to%20be%2C%20you%20might%20simply%20use%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DARRAYTOTEXT(A2%3AC2%2C1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3480534%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20on%20CONCAT%20function%20for%20multiple%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3480534%22%20slang%3D%22en-US%22%3EIf%20I%20use%20Arraytotext%20getting%20like%20below%3CBR%20%2F%3E%7B%22B972%22%2C2%2C%22DPP%22%7D%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3484493%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20on%20CONCAT%20function%20for%20multiple%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3484493%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1416035%22%20target%3D%22_blank%22%3E%40Srikanth0990%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%22('%22%20%26amp%3B%20TEXTJOIN(%22'%2C'%22%2C%201%2C%20A2%3AC2)%20%26amp%3B%20%22')%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3490825%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20on%20CONCAT%20function%20for%20multiple%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3490825%22%20slang%3D%22en-US%22%3EIt%20works%2C%20Thank%20you!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3490874%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20on%20CONCAT%20function%20for%20multiple%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3490874%22%20slang%3D%22en-US%22%3EHow%20it%20works%20if%20I%20include%20COL3%3CBR%20%2F%3E%3CBR%20%2F%3ECOL1%20COL2%20COL3%20COL4%20Result%3CBR%20%2F%3EB972%2002%20DPP%20INSERT%20INTO%20TABLE%20VALUES%20INSERT%20INTO%20TABLE%20VALUES('B972'%2C'02'%2C'DDP')%3CBR%20%2F%3EB9FD%2002%20DPP%20INSERT%20INTO%20TABLE%20VALUES%20INSERT%20INTO%20TABLE%20VALUES('B9FD'%2C'02'%2C'DDP')%3CBR%20%2F%3EB976%20AB%20DPP%20INSERT%20INTO%20TABLE%20VALUES%20INSERT%20INTO%20TABLE%20VALUES('B976'%2C'AB'%2C'DDP')%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3492326%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20on%20CONCAT%20function%20for%20multiple%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3492326%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1416035%22%20target%3D%22_blank%22%3E%40Srikanth0990%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESorry%2C%20I%20didn't%20catch.%20Do%20you%20mean%20it%20shall%20be%20like%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20503px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20503px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20503px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20503px%3B%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20503px%3B%22%3E%3Cspan%20class%3D%22lia-inline-image-display-wrapper%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20503px%3B%22%3E%3Cimg%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F379278iE7167BA5A14E79C4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2Fspan%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3492581%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20on%20CONCAT%20function%20for%20multiple%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3492581%22%20slang%3D%22en-US%22%3EI%20need%20to%20include%20this%20INSERT%20INTO%20TABLE%20VALUES%20in%20Result%201%20columns%3CBR%20%2F%3E%3CBR%20%2F%3EResult1%20should%20be%3CBR%20%2F%3EINSERT%20INTO%20TABLE%20VALUES('B972'%2C'02'%2C'DDP')%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3492609%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20on%20CONCAT%20function%20for%20multiple%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3492609%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1416035%22%20target%3D%22_blank%22%3E%40Srikanth0990%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%22INSERT%20INTO%20TABLE%20VALUES('%22%20%26amp%3B%20TEXTJOIN(%22'%2C'%22%2C%201%2C%20A2%3AC2)%20%26amp%3B%20%22')%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3492672%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20on%20CONCAT%20function%20for%20multiple%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3492672%22%20slang%3D%22en-US%22%3EThanks%20a%20ton!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3492707%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20on%20CONCAT%20function%20for%20multiple%20column%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3492707%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1416035%22%20target%3D%22_blank%22%3E%40Srikanth0990%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor
COL1COL2COL3CONCAT RESULT
B97202DPP('B972','02','DPP');
    
    
    
13 Replies

Hi @Srikanth0990 

 

This is the formula for using concat is this, Concat(col1,col2,col3), and 

=CONCAT(A2,B2,C2)

 

if you want to add space between values:

=CONCAT(A2," ",B2," ",C2)

@Jihad Al-Jarady It's not working as expected

best response confirmed by Srikanth0990 (Occasional Contributor)
Solution
Do you want the result to be this: ('B972','02','DPP');
If yes, this the formula
=CONCAT("('",A1,"','",B1,"','",C1,"');")

@Srikanth0990 

Depending on how precise you need the result to be, you might simply use:

=ARRAYTOTEXT(A2:C2,1)
If I use Arraytotext getting like below
{"B972",2,"DPP"}

@Srikanth0990 

As variant

="('" & TEXTJOIN("','", 1, A2:C2) & "')"

 

It works, Thank you!
How it works if I include COL3

COL1 COL2 COL3 COL4 Result
B972 02 DPP INSERT INTO TABLE VALUES INSERT INTO TABLE VALUES('B972','02','DDP')
B9FD 02 DPP INSERT INTO TABLE VALUES INSERT INTO TABLE VALUES('B9FD','02','DDP')
B976 AB DPP INSERT INTO TABLE VALUES INSERT INTO TABLE VALUES('B976','AB','DDP')

@Srikanth0990 

Sorry, I didn't catch. Do you mean it shall be like

image.png

?

I need to include this INSERT INTO TABLE VALUES in Result 1 columns

Result1 should be
INSERT INTO TABLE VALUES('B972','02','DDP')

@Srikanth0990 

That could be

="INSERT INTO TABLE VALUES('" & TEXTJOIN("','", 1, A2:C2) & "')"