Prev: How to get result by using CountIF, if there are 3 conditions to b
Next: Help with a bsic formula
From: LM on 5 Jul 2008 08:26
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.

From: Don Guillett on 5 Jul 2008 09:37
This should do it. The 6 refers to column F which is one column to the left
of the 1st column. So, if your data starts in col B and you want the new
string in col a, use 1 instead of 6. Correct if you have word-wrap.

Sub joinwithoutblanks()
mc = 6
For c = 3 To Cells(Rows.Count, mc + 1).End(xlUp).row
Cells(c, mc) = ""
For i = mc + 1 To Cells(c, Columns.Count).End(xlToLeft).Column
If Cells(c, i) <> "" Then
Cells(c, mc) = Cells(c, mc) & "," & Cells(c, i)
End If
Next i
Cells(c, mc) = "'" & Right(Cells(c, mc), Len(Cells(c, mc)) - 1)
Next c
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1(a)austin.rr.com
"LM" <LM(a)discussions.microsoft.com> wrote in message
news:97269E41-0635-4799-91B0-D609A90E663C(a)microsoft.com...
>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.
>

From: Teethless mama on 5 Jul 2008 10:09
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.
>
From: Ron Rosenfeld on 5 Jul 2008 11:03
On Sat, 5 Jul 2008 07:09:01 -0700, Teethless mama
<Teethlessmama(a)discussions.microsoft.com> wrote:

>Download and install the free add-in Morefunc.xll from:
>http://xcell05.free.fr/english/
>
>...then use this formula
>

Unfortunately, that link has been down for a number of weeks for me. Are you
able to access it?
--ron
From: LM on 5 Jul 2008 11:04
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.
> >
 |  Next  |  Last
Pages: 1 2 3
Prev: How to get result by using CountIF, if there are 3 conditions to b
Next: Help with a bsic formula