Append values and remove text

  • Thread starter Thread starter PurpleMilk
  • Start date Start date
P

PurpleMilk

Hi,

I have a number of columns - some have values and some don't. I wish to
append only the columns with values and have comma separators.

Is there an easy way to do this? Currently, I do this in 4 steps but I'm
hoping there is an easier way.

1) Column EQ has a formula to append values in columns R thru Z
2) Column ER looks at EQ and substitutes " ," with blanks. Unfortunately ER
always ends with ", " PLUS if only one column has any values, then ER also
starts with " ,"

So to fix this, I do 2 more steps:
3) Column ES looks at ER to remove any values starting with ", "
4) Column ET looks at ES to remove ", " at the end

Thanking you in advance for any help you can provide.
 
Take a look at the SUBSTITUTE function in XL Help. It will allow you
to change ", ," to just "," to remove the intermediate blanks, so you
could apply this to your formula in column EQ. You can also put
another SUBSTITUTE around that to change " ," to blank, and a further
one to change ", " to blank, all in the one formula.

Hope this helps.

Pete
 
Hi,

I have a number of columns - some have values and some don't. I wish to
append only the columns with values and have comma separators.

Is there an easy way to do this? Currently, I do this in 4 steps but I'm
hoping there is an easier way.

1) Column EQ has a formula to append values in columns R thru Z
2) Column ER looks at EQ and substitutes " ," with blanks. Unfortunately ER
always ends with ", " PLUS if only one column has any values, then ER also
starts with " ,"

So to fix this, I do 2 more steps:
3) Column ES looks at ER to remove any values starting with ", "
4) Column ET looks at ES to remove ", " at the end

Thanking you in advance for any help you can provide.
Click to expand...

You could do this easily with a UDF.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic
Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=ConcatRange(R1:Z1)

(Note that the Separator argument is optional and defaults to a ",".)

in some cell.

===============================================
Option Explicit
Function ConcatRange(rg As Range, Optional Separator As String = ",") As String
Dim c As Range
Dim v() As String
Dim i As Long

i = 0
ReDim v(0 To WorksheetFunction.CountA(rg) - 1)
For Each c In rg
If Len(c) > 0 Then
v(i) = c.Value
i = i + 1
End If
Next c
ConcatRange = Join(v, Separator)
End Function
==============================================
--ron
 
Thank you. I didn't realize you could use multiple substitute statements in
one formula.
 
Thanks for the feedback. For this case, I'm going to try the substitute
statement from Pete first as I'm trying to keep the functions simple to be
able to pass on to other team members less familiar with Excel - but may use
this for another project I'm working on. :)
 
Back
Top