Hi! I've a question about how to combine text under certain condition.
I would like to generate combine text result in column G given that column A to E are not equal to 0. Column A to E have value of either text or 0.

Example: G1 will have the result as ND LCSD, G3 will be TP LCSD...etc.

A B C D E F G
0 ND LCSD 0 0 0
0 ND LCSD 0 0 0
TP 0 LCSD 0 0 0

Does anyone knows how to write the formula to generate the result in G?

Many thanks!!!

Hi,

Am Sun, 25 Mar 2012 10:45:50 +0000 schrieb chasiubao:

Example: G1 will have the result as ND LCSD, G3 will be TP LCSD...etc.

A B C D E F G
0 ND LCSD 0 0 0
0 ND LCSD 0 0 0
TP 0 LCSD 0 0 0

try:
=TRIM(SUBSTITUTE(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1,0,""))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Sun, 25 Mar 2012 10:45:50 +0000, chasiubao wrote:


Hi! I've a question about how to combine text under certain condition.
I would like to generate combine text result in column G given that
column A to E are not equal to 0. Column A to E have value of either
text or 0.

Example: G1 will have the result as ND LCSD, G3 will be TP LCSD...etc.

A B C D E F G
0 ND LCSD 0 0 0
0 ND LCSD 0 0 0
TP 0 LCSD 0 0 0

Does anyone knows how to write the formula to generate the result in G?

Many thanks!!!

Straightforward concatenating IF's:

G1: =TRIM(IF(A1=0,"",A1)& IF(B1=0,""," "&B1)&IF(C1=0,""," "&C1)&IF(D1=0,""," "&D1)&IF(E1=0,""," "&E1))

On Sun, 25 Mar 2012 15:52:49 +0200, Claus Busch wrote:


try:
=TRIM(SUBSTITUTE(A1&" "&B1&" "&C1&" "&D1&" "&E1&" "&F1,0,""))


Nice. I like that approach.