Append values and remove text
 

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.

Append values and remove text
 

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



On Dec 14, 4:58*pm, PurpleMilk
wrote:
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.

Append values and remove text
 

Thank you. I didn't realize you could use multiple substitute statements in
one formula.

"Pete_UK" wrote:

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



On Dec 14, 4:58 pm, PurpleMilk
wrote:
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.

.