Solved

Merge and preserve formatting

Posted on 2016-09-17
4
65 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

821 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