Hi i have excel data like this
so i would like to have a formula field which will combine all string where it has a 'X' marked.
from example above is: "Science;Bio;Math"
1
-
Please review the thoughtful answers given below.Tim Biegeleisen– Tim Biegeleisen2015-08-28 06:43:17 +00:00Commented Aug 28, 2015 at 6:43
Add a comment
|
2 Answers
The only thing the Concatenate() function does is make you type more. You can do without it.
But you need to make sure there is no leading ; character if the first cell(s) don't have an X. That can be done by always starting with a ; character and then using the Replace() function to replace the starting ; character.
=REPLACE(IF(A2="x",";"&A1,"")&IF(B2="x",";"&B1,"")&IF(C2="x",";"&C1,"")&IF(D2="x",";"&D1,""),1,1,"")
2 Comments
teylyn
what error? See the screenshot with the formula in action. Do you use the comma or the semicolon as the list separator? You need to accommodate for that. With the comma as the list separator the formula works. Check your setup if it does not.
teylyn
I just copied the formula again from the post and pasted into Excel. It works.
Try entering this formula into cell F2:
=REPLACE(CONCATENATE(IF(A2 = "x", CONCATENATE(";", A1), ""),
IF(B2 = "x", CONCATENATE(";", B1), ""),
IF(C2 = "x", CONCATENATE(";", C1), ""),
IF(D2 = "x", CONCATENATE(";", D1), "")), 1, 1, "")
Here is the formula on a single line:
=REPLACE(CONCATENATE(IF(A2 = "x", CONCATENATE(";", A1), ""), IF(B2 = "x", CONCATENATE(";", B1), ""), IF(C2 = "x", CONCATENATE(";", C1), ""), IF(D2 = "x", CONCATENATE(";", D1), "")), 1, 1, "")
Output
Science;Bio;Math
1 Comment
teylyn
this formula will result in a leading
; sign if A2 does not have an x