Solved

Merge and preserve formatting

Posted on 2016-09-17
4
48 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
Comment Utility
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 20

Expert Comment

by:Ejgil Hedegaard
Comment Utility
Try this, result on Sheet2.
Concatenate-keep-cell-format.xlsm
0
 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
Comment Utility
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
Comment Utility
@-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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 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

10 Experts available now in Live!

Get 1:1 Help Now