Solved

Concatenating cells in Excel

Posted on 1998-10-21
7
597 Views
Last Modified: 2008-02-01
I'm looking to join text from cells A1 and A2 where,
A1 = My name is
A2 = Peter

where Peter is in ITALICS.  The problem is that the concatenated text loses the formatting.  Any suggestions as to what I can do?
0
Comment
Question by:macroman_1
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 7

Expert Comment

by:spiridonov
ID: 1614105
I don't think you can do that. Concatenated text returned from CONCATENATE function is a single value,you can't apply different formatting to a part of it
0
 
LVL 3

Expert Comment

by:frazer
ID: 1614106
Hi,

Yes you can!!

You can't use a spreadsheet or user defined function to do the job but you can use an ordinary sub.

Find below a sub called concat and one called test.

Put your two words in Range("A1") and ("B1") with what ever formatting you want then run Test...

This will copy with formatting into the range("C5:D7")

All of the ranges are adjustable to your needs.

Option Explicit
Sub test()
    concat Range("A1"), Range("B1"), Range("C5:D7")
End Sub
Sub concat(First As Range, Second As Range, RangeToApply As Range)
    Dim temp
    Dim a As Range
    temp = First.Value & " " & Second.Value
    For Each a In RangeToApply
        a.Value = temp
        With a.Characters(Start:=1, Length:=Len(First)).Font
            .Name = First.Font.Name
            .FontStyle = First.Font.FontStyle
            .Size = First.Font.Size
            .Strikethrough = First.Font.Strikethrough
            .Superscript = First.Font.Superscript
            .Subscript = First.Font.Subscript
            .Shadow = First.Font.Shadow
            .Underline = First.Font.Underline
            .ColorIndex = First.Font.ColorIndex
        End With
        With a.Characters(Start:=Len(First) + 2, Length:=Len(Second)).Font
            .Name = Second.Font.Name
            .FontStyle = Second.Font.FontStyle
            .Size = Second.Font.Size
            .Strikethrough = Second.Font.Strikethrough
            .Superscript = Second.Font.Superscript
            .Subscript = Second.Font.Subscript
            .Shadow = Second.Font.Shadow
            .Underline = Second.Font.Underline
            .ColorIndex = Second.Font.ColorIndex
        End With
    Next
End Sub

If you like this answer reject spiridonov's and I'll repost as an answer.

Hope this helps

Frazer

p.s.
spiridonov if you are not sure of an answer post as a comment instead.
0
 
LVL 7

Expert Comment

by:spiridonov
ID: 1614107
I was sure about the answer. But, of course, macroman_l should rejcect it.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 3

Accepted Solution

by:
frazer earned 200 total points
ID: 1614108
Hi,

did it work ok for you?

Frazer
0
 

Author Comment

by:macroman_1
ID: 1614109
Hi Frazer,

I made the following modifications to your code and it does work. The only problem is I have to do it for a range of cells (from C1:C45). Could you help me with the looping structure needed. I'm appending the code I have:


Option Explicit
     Sub Concatenate()
          concat ActiveCell, ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 2)
     End Sub
     Sub concat(First As Range, Second As Range, RangeToApply As Range)
         Dim temp
         Dim a As Range
         temp = First.Value & " " & Second.Value
         For Each a In RangeToApply
             a.Value = temp
             With a.Characters(Start:=1, Length:=Len(First)).Font
                 .Name = First.Font.Name
                 .FontStyle = First.Font.FontStyle
                 .Size = First.Font.Size
                 
             End With
             
             With a.Characters(Start:=Len(First) + 2, Length:=Len(Second)).Font
                 .Name = Second.Font.Name
                 .FontStyle = Second.Font.FontStyle
                 .Size = Second.Font.Size
           
             End With
         Next
     End Sub





0
 
LVL 3

Expert Comment

by:frazer
ID: 1614110
Hi,

try this,

Option Explicit
Sub Concatenate()
    concat Range("A1:A8")
End Sub
Sub concat(RangeToApply As Range)
    Dim temp
    Dim a As Range
    For Each a In RangeToApply
        temp = a.Value & " " & a.Offset(0, 1).Value
        a.Offset(0, 2).Value = temp
        With a.Offset(0, 2).Characters(Start:=1, Length:=Len(a.Value)).Font
            .Name = a.Font.Name
            .FontStyle = a.Font.FontStyle
            .Size = a.Font.Size
        End With
        With a.Offset(0, 2).Characters(Start:=Len(a.Value) + 2, Length:=Len(a.Offset(0, 1).Value)).Font
            .Name = a.Offset(0, 1).Font.Name
            .FontStyle = a.Offset(0, 1).Font.FontStyle
            .Size = a.Offset(0, 1).Font.Size          
        End With
    Next
End Sub


This will take a range ("A1:A8"), Look at the first cell in the range ("A1") concatenate it to ("B1") and put the answer in ("C1"), it will then go onto ("A2") etc.......


Hope this helps

Frazer
0
 

Author Comment

by:macroman_1
ID: 1614111
Thanks a lot -- it worked great.
0

Featured Post

Technology Partners: 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

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

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