CPSRI
asked on
concatenating two columns data excel
Hi,
I have an Excel sheet, in that I have a column header and it has merged of 3 columns, we can consider those 3 columns as sub-headers for the main column header. Now I want to make these 3 columns into one column without loosing their values. If there is no data/values in the beside column thats not a problem at all, but if there is any data/value in beside column I want to concatenate them with a small dash symbol. How can i do this? Please help me.
Thanks in advance.
I have an Excel sheet, in that I have a column header and it has merged of 3 columns, we can consider those 3 columns as sub-headers for the main column header. Now I want to make these 3 columns into one column without loosing their values. If there is no data/values in the beside column thats not a problem at all, but if there is any data/value in beside column I want to concatenate them with a small dash symbol. How can i do this? Please help me.
Thanks in advance.
ASKER
No, its not what i am looking for, i want to make them one and delete those 3 columns, and it should check whether its empty, if it has some contents then it should add a '-' symbol, if there is no content it should leave blank.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
wow.it worked out for me, thank you so much, and one more thing i need. what if i have to concatenate 2 or 4 columns?
For two columns (assuming A & B):
=IF(A2<>"",A2&IF(B2<>"","- ",""),"")& IF(B2<>"", B2,"")
For four columns (assuming A,B,C,D), deep breath now:
=IF(A17<>"",A17&IF(B17<>"" ,"-","")," ")&IF(B17< >"",B17&IF (C17<>""," -","")&C17 ,IF(C17<>" ",IF(A17<> "","-","") &C17,""))& IF(D17<>"" ,IF(OR(A17 <>"",B17<> "",C17<>"" ),"-"&D17, D17),"")
-Glenn
=IF(A2<>"",A2&IF(B2<>"","-
For four columns (assuming A,B,C,D), deep breath now:
=IF(A17<>"",A17&IF(B17<>""
-Glenn
Sorry I misunderstood. You said you wanted to delete the columns. A formula can't delete columns. That would require a macro. Hopefully using the ampersand operator helped contribute to the solution.
ASKER
thank you
So, =A1&B1 would display the contents of those cells together.
If you wanted to concatenate the contents of two cells with a - between them, it would look like this:
=A1&" - "&B1
Does that sound like what you are looking for?