?
Solved

Merge and preserve formatting

Posted on 2016-09-17
4
Medium Priority
?
96 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 24

Expert Comment

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

Accepted Solution

by:
Rob Henson earned 2000 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 27

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

850 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