Join text
 

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

Join text
 

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

Join text
 

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.


--
Thiem
------------------------------------------------------------------------
Thiem's Profile: http://www.excelforum.com/member.php...o&userid=27474
View this thread: http://www.excelforum.com/showthread...hreadid=495454

Join text
 

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

Join text
 

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" wrote in
message ...

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

Join text
 

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


--
Thiem
------------------------------------------------------------------------
Thiem's Profile: http://www.excelforum.com/member.php...o&userid=27474
View this thread: http://www.excelforum.com/showthread...hreadid=495454

Join text
 

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

Merry Chrismas to you.


--
Robert57
------------------------------------------------------------------------
Robert57's Profile: http://www.excelforum.com/member.php...o&userid=29838
View this thread: http://www.excelforum.com/showthread...hreadid=495454

Join text
 

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