SOLVED

Textjoin or simple "&" returns duplicate results when used on Table

Copper Contributor

I've used Textjoin on multiple occasions without problem, or its simple equivalent, the humble ampersand. If we use the below example:

 

Cell A1: 10

Cell B1: James

Cell C1: ()

 

Let's say I use the "," delimiter in Textjoin or add that criteria in a simple "&". In all cases I shold end up with: 10,James,()

 

But for some reason, when applying either of these solutions to a named Table in desktop Excel app, I keep getting this result: 10,James,()10,James,() minus the colors obviously.

 

In other words, the results of either formula are completely duplicated, only omitting the middle ",". Doesn't even matter which approach I choose, and there aren't any formulas running in the cells that I am referencing.

 

Has anyone experienced anything similar?

 

 

5 Replies

@Sevoris 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

@Hans Vogelaar 

 

Yes, I was able to replicate the TextJoin issue by copying the table over to a new Excel file and renaming items.

 

On the other hand I was unable to replicate the Ampersand issue, which returns the same result, in the new Excel file, but I am still getting the same erroneous result if I replace the current TextJoin formula with the ampersand-based one.

 

best response confirmed by Sevoris (Copper Contributor)
Solution

@Sevoris 

Horizontal Alignment has been set to Fill. This causes Excel to repeat the cell value to fill the cell.

HansVogelaar_0-1713888181278.png

Set Horizontal Alignment to General to solve the problem.

 

Thanks, that worked! I didn't imagine that would cause such an issue.

On a related note, as this seems to have caused the issue, is there a way to stop cell contents overflowing if I shrink the column width, without using the Fill option in Alignment?

@Sevoris 

One option is to turn on Wrap Text. This may increase the row height to fit the text.

Another is to turn on Shrink to Fit in the Alignment tab of the Format Cells dialog.

1 best response

Accepted Solutions
best response confirmed by Sevoris (Copper Contributor)
Solution

@Sevoris 

Horizontal Alignment has been set to Fill. This causes Excel to repeat the cell value to fill the cell.

HansVogelaar_0-1713888181278.png

Set Horizontal Alignment to General to solve the problem.

 

View solution in original post