TextJoin in a table doesn't seem to work

I have a sheet that is formatted as a table, 3 columns that I want to join...First Name, MI, and Last Name. Not all fields have a MI. All 3 columns are formatted as text fields.

Here's the formula: =TEXTJOIN(" ",TRUE,D2,F2,E2)

For some reason it doesn't join the 3 columns in a fourth column. Concatinate doesn't work as well.

I'm using EXCEL 16 from Office 365.

Any ideas?

Larry

Doesn't join means the formula was left in the cell.

However, I tried your suggestion with concatenate and it worked. I had to put a " " after the D2 to add a space before the Middle initial. The disadvantage to this method is that if there is no Middle initial, it puts 2 spaces between the first and last name.

Thanks,

This result is acceptable.

Larry

so use TRIM(CONCATENATE(...))

Cells were all formatted text
Calculation is automatic
Sheet is not set to display formulas.

Any other ideas? This has happened to me on a couple other spreadsheets.

Larry

Thanks. That did it.

Larry

Originally Posted by Bearco

Cells were all formatted text

Well, that's the classic cause right there. Although if that's true the CONCATENATE formula shouldn't have worked either.

Originally Posted by FlameRetired

I have not found a reason to format anything as 'Text' ... so far.

Credit card numbers.

Originally Posted by FlameRetired

Ahhh! I never thought of that. Thank you. Mystery solved.

Here's another one: WBS numbers. They are decimalized outline notation like:

1
1.1
1.1.1
1.1.2
1.2
1.2.1
1.2.2
2
2.1

And when there are zero or one decimal points, Excel treats them like numbers, but when more than 1, like text. I want them all to be treated like text so they sort properly.