Combining Cells

[fedas18]

I would like to combine 3 cells into 1.

A1 B1 C1 into D1

A1 B1 C1 D1

NR Aaa AAA Aaa/AAA

Ideally the formula would ignore whenever it says NR. However I am not sure if that is possible.

Example:

AA- NR AA- AA-/AA-

AAA Aaa NR AAA/Aaa



Thanks

 

[Desu Nota from Columbus]

=IF(AND(A1<>"nr",B1<>"nr",C1<>"nr"),CONCATENATE(A1,"/",B1,"/",C1)),IF(AND(A1="nr",B1<>"nr",C1<>"nr"),CONCATENATE(B1,"/",C1)),IF(AND(A1<>"nr",B1="nr",C1<>"nr"),CONCATENATE(A1,"/",C1)),IF(AND(A1<>"nr",B1<>"nr",C1="nr"),CONCATENATE(A1,"/",B1),"")

 

[fedas18]

Says You've entered too many arguments for this function.

Any way around that?

 

[Desu Nota from Columbus]

Says You've entered too many arguments for this function.

Any way around that?

That means the parenthesis are messed up, i just edited the formula see if that works for you. For some reason, my excel is not functioning (i need to reset my comp) so I haven't tested this formula, but I believe the logic is correct.

 

[Desu Nota from Columbus]

Says You've entered too many arguments for this function.

Any way around that?

Here it is: I just verified it!

=IF(AND(A1<>"nr",B1<>"nr",C1<>"nr"),CONCATENATE(A1,"/",B1,"/",C1),IF(AND(A1="nr",B1<>"nr",C1<>"nr"),CONCATENATE(B1,"/",C1),IF(AND(A1<>"nr",B1="nr",C1<>"nr"),CONCATENATE(A1,"/",C1),IF(AND(A1<>"nr",B1<>"nr",C1="nr"),CONCATENATE(A1,"/",B1),""))))

 

[njimack]

Excel Workbook
	A	B	C	D
1	NR	Aaa	AAA	Aaa/AAA
Sheet1

UDF:

Function Combine_NR(r As Range, Optional d As String)

Application.Volatile

Dim x As Range
Dim s As String

For Each x In r
    If x <> "NR" Then
        s = s & x
        If d <> "" Then s = s & d
    End If
Next x

If d <> "" Then
    Combine_NR = Left(s, Len(s) - 1)
Else: Combine_NR = s
End If


End Function

 

[fedas18]

Thank you both so much!

You guys are awesome!

 

[syntaxed]

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(a1&"/"&b1&"/"&c1, "NR/",""),"/NR/",""),"/NR","")

 

[fedas18]

Stupid questions: What does <> mean?

 

[syntaxed]

<> means not equal to.