3 Contact Numbers along with , (coma) Too many arguments

Hi Guys Good Evening from Sri Lanka,

I need a UDF or a formula for the below Argument.

Cell A1 = Mobile Contact Number
Cell A2 = Mobile Contact Number
Cell A3 = Mobile Contact Number
Cell A4 =(,) << A coma is mentioned here.

I get to fill lots and lots of Customer Information for a day.

Case 1 - If No Customer Number is available I will just Type "N/A" on Cell A1, then Outcome should be in B1 = N/A
Case 2 - If 1 Customer Number is available I will just Type the No. on Cell A1, then Outcome should be in B1 = The Number in A1
Case 3 - If 2 Customer Numbers are available I will just Type the No.'s on Cell A1 & A2, then Outcome should be in B1 = A1<Space><coma>A2
Case 4 - If 3 Customer Numbers are available I will just Type the No.'s on Cell A1, A2 & A3 then Outcome should be in B1 = A1<Space><coma>A2<Space><coma>A3

Is this Possible or am I asking for too much? :(

Please help.

Thank You.

try this function:

[VBA]Function combi(inarr As Range)
If inarr.Columns.Count = 1 And inarr.Rows.Count = 3 Then


If inarr(1, 1) = "N/A" Then
combi = inarr(1, 1)
Else
txtout = ""
Rhtxt = ""
For i = 1 To 3
If Len(inarr(i, 1)) > 0 Then
txtout = txtout & Rhtxt & inarr(i, 1)
Rhtxt = " ,"
Else
Exit For
End If
Next i
combi = txtout
End If
Else
combi = "Error, you must select 1 column with 3 rows) "
End If
End Function


[/VBA]

Quote:

---

Originally Posted by offthelip

try this function:

[VBA]Function combi(inarr As Range)
If inarr.Columns.Count = 1 And inarr.Rows.Count = 3 Then


If inarr(1, 1) = "N/A" Then
combi = inarr(1, 1)
Else
txtout = ""
Rhtxt = ""
For i = 1 To 3
If Len(inarr(i, 1)) > 0 Then
txtout = txtout & Rhtxt & inarr(i, 1)
Rhtxt = " ,"
Else
Exit For
End If
Next i
combi = txtout
End If
Else
combi = "Error, you must select 1 column with 3 rows) "
End If
End Function


[/VBA]

---

Hi Thank you for looking into my matter, I managed to solve the case by :

=SUBSTITUTE(TRIM(A1&" "&A2&" "&A3)," ",A4)