Prev: How to get result by using CountIF, if there are 3 conditions to b
Next: Help with a bsic formula
From: sajay on 7 Jul 2008 09:09
dear lyn

try this
=SUBSTITUTE(A2&","&B2&","&C2&","&D2&","&E2&","&F2&","&G2 upto 25th column
,",,",",")




"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: Spiky on 7 Jul 2008 12:16
Perhaps this would help?

http://www.download.com/Morefunc/3000-2077_4-10423159.html
First  |  Prev  | 
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