Avoid comma displaying

[Pat]

=(AV11&","&AX11&","&AZ11&","&BB11)

returns

T1,C4,,F2

because AZ11 does not have a value the comma separator displays anyway, what
do I need to change so the comma does not display?

 

[Mark Graesser]

Pat
The following formula makes the commas conditional upon the following cell

=AV11&IF(AX11="","",",")&AX11&IF(AZ11="","",",")&AZ11&IF(BB11="","",",")&BB1

If the follwoing cell is blank the comma is not used

Good Luck
Mark Graesse
(e-mail address removed)

----- Pat wrote: ----

=(AV11&","&AX11&","&AZ11&","&BB11

return

T1,C4,,F

because AZ11 does not have a value the comma separator displays anyway, wha
do I need to change so the comma does not display

 

[Gord Dibben]

Pat

This User Defined Function will leave out the extra comma(s).

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sBuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sBuf = sBuf & cell.text & ","
Next
ConCatRange = Left(sBuf, Len(sBuf) - 1)
End Function

Usage is: =ConCatRange(selected range)

Gord Dibben Excel MVP

 

[Harlan Grove]

=(AV11&","&AX11&","&AZ11&","&BB11)

returns

T1,C4,,F2

because AZ11 does not have a value the comma separator displays anyway, what
do I need to change so the comma does not display?

Since no one's given the simplest, shortest, quickest answer yet, why not now?

=SUBSTITUTE(TRIM(AV11&" "&AX11&" "&AZ11&" "&BB11)," ",",")

 

[Harlan Grove]

This User Defined Function will leave out the extra comma(s).

Function ConCatRange(CellBlock As Range) As String
Dim cell As Range
Dim sBuf As String
For Each cell In CellBlock
If Len(cell.text) > 0 Then sBuf = sBuf & cell.text & ","
Next
ConCatRange = Left(sBuf, Len(sBuf) - 1)
End Function

Usage is: =ConCatRange(selected range) ...

...

Several things.

-UDFs make little to no sense for anything that could easily be accomplished
using built-in worksheet functions.

-The udf above will work, but the usage example isn't well-devised. The OP's
formula clearly shows that he's referencing nonadjacent cells. The udf call
would need to be

=ConCatRange((AV11,AX11,AZ11,BB11))

-The udf above bombs (returns #VALUE!) if all cells in its range argument are
blank or evaluate to "" because in that case it shouldn't make the final Left()
call.

 

[Gord Dibben]

Harlan

In-line comments below

...
..

Several things.

-UDFs make little to no sense for anything that could easily be accomplished
using built-in worksheet functions.
Correct.

-The udf above will work, but the usage example isn't well-devised. The OP's
formula clearly shows that he's referencing nonadjacent cells. The udf call
would need to be

"clearly shows" to anyone who read the post properly<g>

Not appropriate usage in this application

=ConCatRange((AV11,AX11,AZ11,BB11))

-The udf above bombs (returns #VALUE!) if all cells in its range argument are
blank or evaluate to "" because in that case it shouldn't make the final Left()
call.

Yes. Very narrow application without error-handling.

Thanks, Gord