Forum Statistics
- Forum Members:
- Total Threads:
- Total Posts: 6
There are 1 users currently browsing forums.
|
 |
07-04-2009, 06:23 AM
|
 |
Valued Forum Contributor
|
|
Join Date: 10 Feb 2009
Location: Croatia
MS Office Version:Excel 2007
Posts: 2,533
|
|
|
If cell has x turn into number and concatenate
Please Register to Remove these Ads
Example is in the attch:
Book11.xls
This formula works but if there is no number 4 then I don't want comma after 1, or 2, or 3,
How avoid it:
results
1,2,3,4
1,2,
1,2,3,
3,4
1,
4
2,3,
(red , need to be removed)
__________________
"Relax. What is mind? No matter. What is matter? Never mind!"
Last edited by zbor; 07-04-2009 at 07:28 AM.
|
07-04-2009, 06:26 AM
|
|
Valued Forum Contributor
|
|
Join Date: 10 Feb 2009
Location: Croatia
MS Office Version:Excel 2007
Posts: 2,533
|
|
|
Re: If cell has x turn into number and concatenate
Of course, you can change formula at will.
__________________
"Relax. What is mind? No matter. What is matter? Never mind!"
|
07-04-2009, 06:30 AM
|
 |
Forum Guru
|
|
Join Date: 23 Jun 2007
Location: London,England
MS Office Version:office 97 ,2003 ,2007
Posts: 6,084
|
|
|
Re: If cell has x turn into number and concatenate
take the , out of the formula?
oh i see you want 1,2 and 1,2,3
but no trailing ,
=LEFT(IF(A2="x","1,","")&IF(B2="x","2,","")&IF(C2="x","3,","")&IF(D2="x","4,",""),LEN(IF(A2="x","1," ,"")&IF(B2="x","2,","")&IF(C2="x","3,","")&IF(D2="x","4,",""))-1)
there must be a shorter way tho.
Last edited by martindwilson; 07-04-2009 at 06:38 AM.
|
07-04-2009, 06:30 AM
|
|
Valued Forum Contributor
|
|
Join Date: 10 Feb 2009
Location: Croatia
MS Office Version:Excel 2007
Posts: 2,533
|
|
|
Re: If cell has x turn into number and concatenate
Just after last number
__________________
"Relax. What is mind? No matter. What is matter? Never mind!"
|
07-04-2009, 06:43 AM
|
|
Forum Guru
|
|
Join Date: 23 Jun 2007
Location: London,England
MS Office Version:office 97 ,2003 ,2007
Posts: 6,084
|
|
|
Re: If cell has x turn into number and concatenate
there you go, add , after every number then remove last character
|
07-04-2009, 07:28 AM
|
|
Valued Forum Contributor
|
|
Join Date: 10 Feb 2009
Location: Croatia
MS Office Version:Excel 2007
Posts: 2,533
|
|
|
Re: If cell has x turn into number and concatenate
I figure it out during lunch 
=LEFT(IF(A3="x";"1,";"")&IF(B3="x";"2,";"")&IF(C3="x";"3,";"")&IF(D3="x";"4";"");2*COUNTA(A3:D3)-1)
__________________
"Relax. What is mind? No matter. What is matter? Never mind!"
|
|
|
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
|
|
|
| Thread Tools |
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
