Solved

Merge and preserve formatting

Posted on 2016-09-17
4
77 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-
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 22

Expert Comment

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

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 26

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

690 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