Join text

[Robert57]

Ok. I will try to explain.
I want to join text from several cells and I'm using a function "Sammafoga(A1;a2;a3)"
But I want the text to be formatted in the recevied cell, i.e. font size from A1 shall be 18, a2 italic and a3 normal.

How do I do that?

tnx for your answer
Robert

[Gary''s Student]

First copy the result of the formula and paste/special as value to an un-used
cell. Then, in the formula bar, select the regions of text and format them
individually
--
Gary's Student


"Robert57" wrote:

>
> Ok. I will try to explain.
> I want to join text from several cells and I'm using a function
> "Sammafoga(A1;a2;a3)"
> But I want the text to be formatted in the recevied cell, i.e. font
> size from A1 shall be 18, a2 italic and a3 normal.
>
> How do I do that?
>
> tnx for your answer
> Robert
>
>
> --
> Robert57
> ------------------------------------------------------------------------
> Robert57's Profile: http://www.excelforum.com/member.php...o&userid=29838
> View this thread: http://www.excelforum.com/showthread...hreadid=495454
>
>

[Thiem]

Function is concatenate, however you will need vba to change the result in the multi format you specify. Will have a look and if I have success, i will let you know. Alleast other users will know what function yo refer to.

[Robert57]

Tnx, but I have 365 cells to manually convert. But maybe I can send the file to you?

That is right I'm trying to make a calender.

regrds
Robert

[Thiem]

Sub Macro6()
'
'
'
Range("I1352").Select
ActiveWindow.LargeScroll Down:=-12
ActiveWindow.ScrollRow = 1
Range("D2").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-3],RC[-2],RC[-1])"
Range("E2").Select
ActiveCell.FormulaR1C1 = ""
Range("D2").Select
Selection.Copy
Range("E2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("E2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("F2").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-5])"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-5])"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=LEN(RC[-5])"
Range("A2").Select
End Sub
Sub Macro7()
'
'

'
Range("E2").Select
ActiveCell.FormulaR1C1 = "bbbbbaaaaarrrrr"
With ActiveCell.Characters(Start:=1, Length:=5).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 16
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=1 + 5, Length:=5).Font
.Name = "Arial"
.FontStyle = "Italic"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=1 + 5 + 5, Length:=5).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("G4:G5").Select
Range("G5").Activate
End Sub

[Bob Phillips]

Robert,

Here is some VBA. Select the cells to concatenate, then run it

Sub Sammanfoga()
Dim cell As Range
Dim stemp
For Each cell In Selection
stemp = stemp & Format(cell.Value, cell.NumberFormat)
Next cell
Set cell = Application.InputBox("select cell with mouse for concatenated
values", _
Type:=8)
cell.Value = stemp
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Robert57" <[email protected]> wrote in
message news:[email protected]...
>
> Tnx, but I have 365 cells to manually convert. But maybe I can send the
> file to you?
>
> That is right I'm trying to make a calender.
>
> regrds
> Robert
>
>
> --
> Robert57
> ------------------------------------------------------------------------
> Robert57's Profile:
http://www.excelforum.com/member.php...o&userid=29838
> View this thread: http://www.excelforum.com/showthread...hreadid=495454
>

[Robert57]

Tnx all of you. I will try it during the chrismas.

Merry Chrismas to you.

[Dave Peterson]

You won't be able to do this kind of thing using a worksheet function (even a
UDF).

But you could use a macro.

This sample just does A1:A3 and puts the formatted string in B1. But depending
on where your data is and how it's laid out, it could be changed to loop through
those cells.

Remember that after the cell is concatenated and formatted, it's not a formula.
Changing A1:A3 won't change B1--you have to run the macro again.

Option Explicit
Sub testme()

Dim myCell As Range
Dim myRng As Range
Dim DestCell As Range
Dim myStr As String
Dim lCtr As Long
Dim iCtr As Long

With ActiveSheet
Set DestCell = .Range("b1")
Set myRng = .Range("a1:a3")
myStr = ""
For Each myCell In myRng.Cells
myStr = myStr & myCell.Text
Next myCell
With DestCell
.NumberFormat = "@"
.Value = myStr
End With
End With

lCtr = 0
For Each myCell In myRng.Cells
For iCtr = 1 To Len(myCell.Text)
lCtr = lCtr + 1
With DestCell.Characters(lCtr, 1).Font
.Name = myCell.Characters(iCtr, 1).Font.Name
.FontStyle = myCell.Characters(iCtr, 1).Font.FontStyle
.Size = myCell.Characters(iCtr, 1).Font.Size
.Strikethrough = myCell.Characters(iCtr, 1).Font.Strikethrough
.Superscript = myCell.Characters(iCtr, 1).Font.Superscript
.Subscript = myCell.Characters(iCtr, 1).Font.Subscript
.OutlineFont = myCell.Characters(iCtr, 1).Font.OutlineFont
.Shadow = myCell.Characters(iCtr, 1).Font.Shadow
.Underline = myCell.Characters(iCtr, 1).Font.Underline
.ColorIndex = myCell.Characters(iCtr, 1).Font.ColorIndex
.Bold = myCell.Characters(iCtr, 1).Font.Bold
.Italic = myCell.Characters(iCtr, 1).Font.Italic
End With
Next iCtr
Next myCell

End Sub

Robert57 wrote:
>
> Ok. I will try to explain.
> I want to join text from several cells and I'm using a function
> "Sammafoga(A1;a2;a3)"
> But I want the text to be formatted in the recevied cell, i.e. font
> size from A1 shall be 18, a2 italic and a3 normal.
>
> How do I do that?
>
> tnx for your answer
> Robert
>
> --
> Robert57
> ------------------------------------------------------------------------
> Robert57's Profile: http://www.excelforum.com/member.php...o&userid=29838
> View this thread: http://www.excelforum.com/showthread...hreadid=495454

--

Dave Peterson