Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have the following rows in my excel spreadsheet.

============================================
| Male    | Jack    | 8765    | India      |
============================================
| Female  | Jill    | 5425    | Japan      |
============================================
| Male    | Pet     | 8541    | China      |
============================================
| Female  | Joan    | 2156    | Australia  |  
============================================

How do I use excel formula to combine the above rows into a single cell. (because the data above might change quite frequently and I do not wish to do it manually.)

======================================
| Male    Jack    8765    India      |
| Female  Jill    5425    Japan      |
| Male    Pet     8541    China      |
| Female  Joan    2156    Australia  |  
======================================

(For your info - I check that Microsoft are able to combine the same content in a row

============================================
| Male    | Jack    | 8765    | India      |
============================================

to a single row

======================================
| Male    Jack    8765    India      |
======================================

using the formula CONCATENATE or & as mention in http://office.microsoft.com/en-us/excel-help/combine-the-contents-of-multiple-cells-HA010248390.aspx?CTT=5&origin=HA010250379.

but I want to use a excel formula to multiple rows into a single cell. Possible?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

Use CONCATENATE and/or & to combine the data within a row and use ...&CHAR(10)&... to insert the line break for the row. Turn on Wrap text for the cell holding the result so that the line break is displayed properly.

Your columns will probably not line up correctly because the cells' contents have variable lengths will probably be displayed with a proportional font. For best results you should use a fixed width font and use the LEN and REPT functions to determine and insert the appropriate amount of spaces between your data. For example, to make sure the data in cell A1 is padded out with spaces to make a column 20 characters wide, ... A1 & REPT(" ", 20-LEN(A1)) & ...

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.