Thanks to both of you. I will try your suggestions tomorrow.

In the meantime, I tried SUBSTITUTE and that seemed to substitute commas for
my spaces and gave me the result I wanted. I am sure I had tried this before
and it didn't work but obviously I did something wrong the first time.

Thanks again.



"Teethless mama" wrote:

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/english/

...then use this formula

=SUBSTITUTE(TRIM(MCONCAT(IF(C2:Z2<"",C2:Z2&" ","")))," ",",")

ctrl+shift+enter, not just enter


"LM" wrote:

I have a list of 2000 rows by 25 columns. I want to concatenate a cells e to
y in each row. Not all cells have data in them but the cells that do have
data in them have a seven digit number.

I need to concatenate all cells with data in them and have them end up in
one column, with each seven digit number divided by a comma, i.e.
1234567,8912345,6789123,4567890.

If I use &","& I end up with extra commas where I have had blank cells.

I tried using spaces when concatenating, i.e. &" "&, removing all trailing
spaces using TRIM and then replacing the remaining spaces with commas but, no
matter how I try to format the column as text, as soon as I replace all the
spaces with commas, Excel turns most of them into numbers that look like this
- 2.02411E+62 and I can't get that back to appearing as a number or text with
commas between each seven numbers.

I tried using the following formula:

=substitute(trim(C2&" "&D2&" "&E2&" "&F2)," ",", ")

which I found in relation to a similar question in this discussion group but
I can't get that formula to work (obviously I have the equations going up to
&Y2) and I can't work out why even that formula won't work.

Any help would be so gratefully appreciated as I have so far spent hours on
this.

Lyn.