Concatenate with style

[DocAElstein]

Here is a simple quick solution idea.
It stores the formulas, then replaces all formulas with their values, then runs Ricks coding, then replaces the formulas

I am using, as an example, a similar example to that at the referenced link ( https://www.mrexcel.com/board/thread.../#post-3953353 )

I have this worksheet example, where I have a formula in the first cell:
RicksConcatWithStylesBefore.JPG : http://i.imgur.com/PlcxFab.jpg
_____ Workbook: RicksConcatWithStyles.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F
1	excel fox	The	thinking	man's	MrEXCEL	Tips & Solutions
2
3
4	excel
5
6
7			fox

Worksheet: Sheet1
_____ Workbook: RicksConcatWithStyles.xls ( Using Excel 2007 32 bit )

Row\Col	A	B	C	D	E	F
1	=Sheet1!A4 & " " & Sheet1!C7	The	thinking	man's	MrEXCEL	Tips & Solutions
2
3
4	excel
5
6
7			fox

Worksheet: Sheet1

After running the macro you will get this:
RicksConcatWithStylesAfter.JPG : http://i.imgur.com/3pvfB7n.jpg


Alan

P.S:
Note: This solution is not perfect. If you have different styles or formatting for characters within a cell then some of the formatting is lost
Example:
Before: http://i.imgur.com/iv5yI4t.jpg

After: http://i.imgur.com/IKxx7nj.jpg

---

Macro here: https://excelfox.com/forum/showthrea...ll=1#post15166
Also macro is in uploaded file, RicksConcatWithStyles.xls : https://app.box.com/s/n3rfg981htv37p5xjfz9y4v17ycoybqe

[Abdul]

Wow nice, Thanks once again for your quick response,
one little problem is there, if any cell contain a number (value) it shows run time error 1004, and if given range contains more than 230 characters VBA not works the destination cell (A3) will become blank.

[DocAElstein]

Hello Abdul

...if any cell contain a number (value) it shows run time error 1004

This change seems to overcome that problem:
Change

.Characters(Position, Len(Cell.Value)).Text = Cell.Characters(1, Len(Cell.Value)).Text

to

.Characters(Position, Len(Cell.Value)).Text = Cell.Value

---

... if given range contains more than 230 characters VBA not works the destination cell (A3) will become blank

I do not understand this problem.

Can you provide a sample to demonstrate the problem?
I will then investigate further

Alan

[Abdul]

I do not understand this problem.

Can you provide a sample to demonstrate the problem?
I will then investigate further

Alan

Please check the attached file,