Solved

Concatenating cells in Excel

Posted on 1998-10-21
7
595 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
  • 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Introduction Perhaps more familiar to developers who primarily use VBScript than to developers who tend to work only with Microsoft Office and Visual Basic for Applications (VBA), the Dictionary is a powerful and versatile class, and is useful …
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

837 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