Solved

Merge and preserve formatting

Posted on 2016-09-17
4
55 Views
Last Modified: 2016-09-26
I'm trying to convert multiple cells of an excel spreadsheet into a single cell, but preserve the formatting. I want to merge the cells in column B into a single cell and preserve the line breaks and as much formatting as possible. I've attached an example spreadsheet below. For cell A1, I want to merge the B1 - B23 cells all into B1 and preserve all the line breaks and formatting. For cell A24, I want to merge the B24 - B38 cells all into B24 and preserve all line breaks and formatting and then both A24 and B24 would move to A2 and B2. I have several thousand rows to merge like this. Is this possible?

I was trying to use the concatenate formula with char(10). It was kind of working, but not quite. Probably operator error.

Any help is appreciated.
Example.xlsx
0
Comment
Question by:-Dman100-
4 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 41803928
My first thought is that you are trying to do a complex text document in Excel, which is not the right application -- that would be Word.  Is there some reason you are using Excel?  Would it be possible to move the data to Word?
0
 
LVL 21

Expert Comment

by:Ejgil Hedegaard
ID: 41803929
Try this, result on Sheet2.
Concatenate-keep-cell-format.xlsm
0
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 41804519
I have this function in some of my workbooks:

Public Function AllComments(Rng As Range)

R = 0
LR = Rng.Cells.Count
For Each Cell In Rng
    Newline = ""
    R = R + 1
    If R < LR Then Newline = Chr(10)
    Collated = Collated & Cell.Text & Newline
Next Cell
    AllComments = Collated
End Function

Open in new window

It concatenates the text from each cell in the specified range with a line break after each.

Copy and paste the above into a standard module and then use as you would a formula:

=AllComments(Range)

Thanks
Rob H
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41815501
@-Dman100

although you have accepted the question, i quote your question "preserve all the line breaks and formatting"
you did not mention just line break but formatting as well.

the UDF "Allcomments" do not bring the formatting of source cells

 perhaps you did not check Ejgil Hedegaard's solution ID: 41803929  which takes care of everything including formatting.

just a thought.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to read BOM (Byte Order Mark) from csv file. 4 35
SQL Server and getting client IP Address 6 51
Excel Score Formula 5 47
Excel - find text within text? 1 21
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now