?
Solved

Concatenating cells in Excel

Posted on 1998-10-21
7
Medium Priority
?
599 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
Independent Software Vendors: 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!

 
LVL 3

Accepted Solution

by:
frazer earned 800 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

762 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