[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Concatenating cells in Excel

Posted on 1998-10-21
7
Medium Priority
?
600 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:Victor 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:Victor Spiridonov
ID: 1614107
I was sure about the answer. But, of course, macroman_l should rejcect it.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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 …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
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 …

650 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