Solved

Concatenating cells in Excel

Posted on 1998-10-21
7
594 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
Outlook Free & Paid Tools
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:
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…

785 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