Combine 3 columns and rename cells with 0 value

[polks111]

Hello,

I have been struggling with this task for a while and thought maybe someone can help me here.
I need to combine data from 3 columns and when combined all cell text where the value is 0 needs to be replaced with words Not affected. Can`t find any suitable formula or VBA code for this.

Data.xlsx
	A	B	C	D	E
1	Result	Payout	Currency	All 3 combined with formula	Needed result
2	Overpaid	34.5	MYR	Overpaid 34.5 MYR	Overpaid 34.5 MYR
3	Overpaid	0	EUR	Overpaid 0 EUR	Not affected
4	Overpaid	0	USD	Overpaid 0 USD	Not affected
5	Overpaid	0	MXN	Overpaid 0 MXN	Not affected
6	Overpaid	0	BRL	Overpaid 0 BRL	Not affected
7	Overpaid	5	BRL	Overpaid 5 BRL	Overpaid 5 BRL
8	Refund	45000	COP	Refund 45000 COP	Refund 45000 COP
9	Overpaid	0.5	JPY	Overpaid 0.5 JPY	Overpaid 0.5 JPY
10	Overpaid	0	MYR	Overpaid 0 MYR	Not affected
11	Overpaid	4	CAD	Overpaid 4 CAD	Overpaid 4 CAD
12	Overpaid	0	GBP	Overpaid 0 GBP	Not affected
13	Refund	60	MXN	Refund 60 MXN	Refund 60 MXN
14	Refund	135	MYR	Refund 135 MYR	Refund 135 MYR
15	Refund	108	GBP	Refund 108 GBP	Refund 108 GBP
16	Refund	6000	INR	Refund 6000 INR	Refund 6000 INR
17	Refund	0.2	MXN	Refund 0.2 MXN	Refund 0.2 MXN
Data

Cell Formulas
Range	Formula
D2:D17	D2	=A2&" " &B2& " " &C2

 

[Fluff]

Hi & welcome to MrExcel.
How about

=IF(B2=0,"No affected",TEXTJOIN(" ",,A2:C2))

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Combine 3 columns and rename cells with 0 value
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[polks111]

Thank you. It works.

 

[Fluff]

You're welcome & thanks for the feedback.