Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

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.
0
CPSRI
Asked:
CPSRI
  • 3
  • 2
  • 2
1 Solution
 
Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
To concatenate two pieces of text, you use the & operator.

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?
0
 
CPSRIAuthor Commented:
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.
0
 
Glenn RayExcel VBA DeveloperCommented:
To concatenate values in three columns - say columns A, B, and C,
AND display that in column D,
AND include a hyphen only when there are values in adjacent columns,

then you would insert the following formula in the first applicable row (for example, row 2)
=IF(A2<>"",A2&IF(B2<>"","-",""),"")&IF(B2<>"",B2&IF(C2<>"","-","")&C2,IF(C2<>"",IF(A2<>"","-","")&C2,""))

This will prevent a hyphen from being inserted as a delimiter if any cell has no value.  This works for all eight possible combinations of blanks/values in three columns.  (I'm sure there's a more elegant solution, but this works.)
===============================================
To complete the action as you described in your second comment, copy all the cells containing the formula (column D in this example), paste special: values, then delete the first three columns.

-Glenn
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CPSRIAuthor Commented:
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?
0
 
Glenn RayExcel VBA DeveloperCommented:
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
0
 
Brian BEE Topic Advisor, Independant Technology ProfessionalCommented:
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.
0
 
CPSRIAuthor Commented:
thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now