How do I make one cell with text from multiple cells?

[Marq]

using 365

I have 3 cells F22,F27,G28....I manually plug text into these cells.

I need all three text to show up in one cell, separated with commas.

I do not need to have "N/A" show up if that's one of the plugs in the 3 cells above.

For example I can have in the 3 cells:

F22= TEST 1
F27= TEST 2
G28= TEST 3

The cell with the formula would show up as TEST 1, TEST 2, TEST 3

and If I have this:

F22= TEST 1
F27= N/A
G28= TEST 3

The cell with the formula would show up as TEST 1, TEST 3...basically im eliminating all N/A's from the string of text

 

[afonsomira]

Hello,
Try this formula:
=IF(F22="N/A",F27 & " , " & G28,IF(F27="N/A",F22 & " , " & G28,IF(G28="N/A",F22 & " , " & F27,F22 & " , " & F27 & " , " & G28)))

 

[Marq]

I have this but it doesn't eliminate the N/A's

=F22&","&F27&","&G28

 

[Fluff]

How about

=TEXTJOIN(",",1,SUBSTITUTE(CHOOSE({1,2,3},F22,F27,G28),"N/A",""))

 

[Marq]

Hello,
Try this formula:
=IF(F22="N/A",F27 & " , " & G28,IF(F27="N/A",F22 & " , " & G28,IF(G28="N/A",F22 & " , " & F27,F22 & " , " & F27 & " , " & G28)))

Its half way working..Im still getting N/A's pop up if I have two N/A's out of the three

 

[Marq]

How about

=TEXTJOIN(",",1,SUBSTITUTE(CHOOSE({1,2,3},F22,F27,G28),"N/A",""))

Thanks! That works....I had to put a space after the comma between the two quotes but besides that it works perfect!

Now that I'm looking at it working, is there a way for the formula that the cell is in to have an existing locked in text and have the 3 choices show up after it?

meaning, in the cell, I want to have this at all times:

Atmosphere:

And once I plug in the three cells it would look like this:

Atmosphere: Test 1, Test 2, Test 3

or maybe

Atmosphere: Test 1, Test 3

 

[Fluff]

How about

="Atmosphere: "&TEXTJOIN(", ",1,SUBSTITUTE(CHOOSE({1,2,3},T(F22),T(F27),T(G28)),"N/A",""))

This will also handle cells without text.

 

[Marq]

How about

="Atmosphere: "&TEXTJOIN(", ",1,SUBSTITUTE(CHOOSE({1,2,3},T(F22),T(F27),T(G28)),"N/A",""))

This will also handle cells without text.

That WORKS!..Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.