Sunday, 28 June 2015

Excel CHAR Function to Consolidate Data from Different Cells into a Single Cell in Multiple Lines

How to Consolidate Data from Different Cells into a Single Cell in Multiple Lines in Excel
Suppose ,If an Employee Address is currently split in different cells, and you wish to consolidate in a single cell in multiple lines, use the excel CHAR function as follows :

Current data split in multiple cells :

The Address is currently split in cells A2 to E2 and the below formula will consolidates and formats into a single cell in multiple lines.

=A2&CHAR(10)&B2&"-"&C2&CHAR(10)&D2&"-"&E2

Here ,10 is the ANSI code (number) for line feed, and it provides the line breaks to format data.

Ensure that the cell text wrapping is enabled, by clicking on "Wrap Text".

Result :


Note :
Suppose If you want to format an Address with line breaks (appearing in multiple lines in cell C3, as in above result) to a single line format, use the below formula : 

=SUBSTITUTE(C3,CHAR(10),", ") 

Thanks
Tamatam

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts