Home

textjoin issues

%3CLINGO-SUB%20id%3D%22lingo-sub-723682%22%20slang%3D%22en-US%22%3Etextjoin%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-723682%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%20%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20the%20text%20join%20formula%20to%20combine%20a%20series%20of%20text%20in%20a%20row.%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20the%20cells%20have%20a%20vlookup%20formula%20to%20get%20data%20from%20another%20sheet.%20When%20try%20to%20join%20the%20text%20resulted%20in%20the%20cells%20by%20the%20vlookup%20formula%2C%20the%20formula%20of%20textjoin%20does%20not%20work.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20image%20i%20have%20added%20a%20better%20understanding%20.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EText%20Join%20%3A%26nbsp%3B%26nbsp%3B%3DTEXTJOIN(%22%20%7C%20%22%3BTRUE%3BIF(COLUMN(F4%3AY4)%3DMATCH(F4%3AY4%3BF4%3AY4%3B0)%3BF4%3AY4%3B%22%22))%3C%2FP%3E%3CP%3EFormula%20in%20the%20cells%20%3A%3DIFNA(VLOOKUP(%24A4%26amp%3BCOLUMNS(F3%3A%24F4)%3B'Reception%20'!%24M%242%3A%24N%2450000%3B2%3B0)%3B%22%22)%3C%2FP%3E%3CTABLE%20border%3D%220%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CSPAN%3EHelp%20please%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-723682%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-724597%22%20slang%3D%22en-US%22%3ERe%3A%20textjoin%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-724597%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367058%22%20target%3D%22_blank%22%3E%40saravana0306%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20guess%20would%20be%3A%3C%2FP%3E%3CPRE%3E%3CSPAN%3E%3DTEXTJOIN(%22%20%7C%20%22%3BTRUE%3BIF(COLUMN(F4%3AY4)-COLUMN(%24F4)%2B1%3DMATCH(F4%3AY4%3BF4%3AY4%3B0)%3BF4%3AY4%3B%22%22))%3C%2FSPAN%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725157%22%20slang%3D%22en-US%22%3ERe%3A%20textjoin%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725157%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20much%20for%20the%20time%20.%20Unfortunately%2C%20this%20one%20does%20not%20work%20as%20well.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20think%20it%20is%20because%20the%20there%20is%20some%20formula%20already%20there%20in%20the%20cell%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725279%22%20slang%3D%22en-US%22%3ERe%3A%20textjoin%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725279%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367058%22%20target%3D%22_blank%22%3E%40saravana0306%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20worked%20for%20me.%3C%2FP%3E%3CP%3ECould%20you%20upload%20your%20file%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-725563%22%20slang%3D%22en-US%22%3ERe%3A%20textjoin%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-725563%22%20slang%3D%22en-US%22%3E%3CP%3EI'd%20wrap%20by%20IFERROR%3C%2FP%3E%0A%3CPRE%3E%3DTEXTJOIN(%22%20%7C%20%22%2CTRUE%2CIFERROR(IF(COLUMN(F4%3AY4)-COLUMN(%24F4)%2B1%3DMATCH(F4%3AY4%2CF4%3AY4%2C0)%2CF4%3AY4%2C%22%22)%2C%22%22))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-727459%22%20slang%3D%22en-US%22%3ERe%3A%20textjoin%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-727459%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%20Sure%20.%20Here%20it%20is.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-728633%22%20slang%3D%22en-US%22%3ERe%3A%20textjoin%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-728633%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367058%22%20target%3D%22_blank%22%3E%40saravana0306%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20be%20kind%20to%20the%20helpers%20and%20do%20not%20upload%20such%20big%20files%20that%20take%20ages%20to%20load%20and%20to%20calculate.%3C%2FP%3E%3CP%3EI%20deleted%20some%20empty%20rows%20and%20some%20formulas%20and%20the%20conditional%20formatting%20because%20they%20were%20all%20slowing%20down%20the%20spreadsheet.%20That%20left%201.7%20MB%20from%20the%20original%209%20MB.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20created%20a%20different%20solution%20using%20Power%20Query%20instead%20of%20Excel's%20TEXTJOIN().%3C%2FP%3E%3CP%3EThe%20result%20is%20still%20shown%20as%20a%20pivot%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-730349%22%20slang%3D%22en-US%22%3ERe%3A%20textjoin%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-730349%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%2C%20My%20sincere%20apologies%20for%20the%20inconvenience%20caused.%20This%20won't%20you%20repeated%20again.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20the%20help%20and%20time.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-734687%22%20slang%3D%22en-US%22%3ERe%3A%20textjoin%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-734687%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367058%22%20target%3D%22_blank%22%3E%40saravana0306%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20investigated%20the%20issues%20you%20mailed%20to%20me.%3C%2FP%3E%3CP%3EThe%20problem%20is%20there%20are%20additional%20blanks%20after%20some%20texts.%20To%20deal%20with%20that%20I%20inserted%20a%20Trimmed%20Text-step%20in%26nbsp%3Btbl_Reception.%3C%2FP%3E%3CP%3ETo%20unify%20the%20order%20of%20the%20text%20blocks%20I%20inserted%20List.Sort%20into%20the%26nbsp%3BExtracted%20Values-step%20in%26nbsp%3Bqry_Reception.%3C%2FP%3E%3CPRE%3E%3D%20Table.TransformColumns(%23%22Added%20Custom%22%2C%20%7B%22Temp%22%2C%20each%20Text.Combine(List.Transform(List.Sort(_)%2C%20Text.From)%2C%20%22%7C%22)%2C%20type%20text%7D)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20it%20helps.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735671%22%20slang%3D%22en-US%22%3ERe%3A%20textjoin%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735671%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%2C%20It%20worked%20fantastically.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20the%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBr.%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESaravana%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735701%22%20slang%3D%22en-US%22%3ERe%3A%20textjoin%20issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735701%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F367058%22%20target%3D%22_blank%22%3E%40saravana0306%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome.%3C%2FP%3E%3C%2FLINGO-BODY%3E
saravana0306
Occasional Contributor

Hello Everyone , 

 

I have the text join formula to combine a series of text in a row. 

But the cells have a vlookup formula to get data from another sheet. When try to join the text resulted in the cells by the vlookup formula, the formula of textjoin does not work.

 

Please see the image i have added a better understanding .

 

Text Join :  =TEXTJOIN(" | ";TRUE;IF(COLUMN(F4:Y4)=MATCH(F4:Y4;F4:Y4;0);F4:Y4;""))

Formula in the cells :=IFNA(VLOOKUP($A4&COLUMNS(F3:$F4);'Reception '!$M$2:$N$50000;2;0);"")

 

Help please 

10 Replies

@saravana0306 

My guess would be:

=TEXTJOIN(" | ";TRUE;IF(COLUMN(F4:Y4)-COLUMN($F4)+1=MATCH(F4:Y4;F4:Y4;0);F4:Y4;""))

 

@Detlef Lewin 

 

Thanks much for the time . Unfortunately, this one does not work as well. 

 

Do you think it is because the there is some formula already there in the cell ?

@saravana0306 

It worked for me.

Could you upload your file?

 

I'd wrap by IFERROR

=TEXTJOIN(" | ",TRUE,IFERROR(IF(COLUMN(F4:Y4)-COLUMN($F4)+1=MATCH(F4:Y4,F4:Y4,0),F4:Y4,""),""))

 

@Detlef Lewin  Sure . Here it is. 

@saravana0306 

Please be kind to the helpers and do not upload such big files that take ages to load and to calculate.

I deleted some empty rows and some formulas and the conditional formatting because they were all slowing down the spreadsheet. That left 1.7 MB from the original 9 MB.

 

I created a different solution using Power Query instead of Excel's TEXTJOIN().

The result is still shown as a pivot table.

 

@Detlef Lewin , My sincere apologies for the inconvenience caused. This won't you repeated again.

 

Thank you very much for the help and time.

@saravana0306 

I investigated the issues you mailed to me.

The problem is there are additional blanks after some texts. To deal with that I inserted a Trimmed Text-step in tbl_Reception.

To unify the order of the text blocks I inserted List.Sort into the Extracted Values-step in qry_Reception.

= Table.TransformColumns(#"Added Custom", {"Temp", each Text.Combine(List.Transform(List.Sort(_), Text.From), "|"), type text})

 

I hope it helps.

 

@Detlef Lewin , It worked fantastically. 

 

Thank you very much for the help.

 

Br.,

 

Saravana

@saravana0306 

You're welcome.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies