Avoid comma displaying

  • Thread starter Thread starter Pat
  • Start date Start date
P

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?
 
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
 
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
 
=(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?
Click to expand...

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

=SUBSTITUTE(TRIM(AV11&" "&AX11&" "&AZ11&" "&BB11)," ",",")
 
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) ...
Click to expand...
...

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.
 
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
Click to expand...

"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.
Click to expand...

Yes. Very narrow application without error-handling.

Thanks, Gord
 
Back
Top