ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   combining 3 cells in 1 on different lines (https://www.excelbanter.com/excel-discussion-misc-queries/241135-combining-3-cells-1-different-lines.html)

Jack Sons
combining 3 cells in 1 on different lines
 
Hi all,

In cell A2 is a name, say Johnson.
In cell B2 a code, say Ab89-w-5
In cell K2 is a date (in European notation) say 16 september 2009
All in Tmes New Roman 10.
Ik want in cell Q2 this

Johnson
Ab89-w-5
16 september 2009

So - on different lines - the name left aligned en both other data right
aligned (if possible)
Also if possible I would like the name in Times New Roman 10 and in bold,
and the rest in Arial 7 (not bold).

I spent a lot of time on it but I can't figure out what code to use and I
wonder if it is possible at all.
If all is not possible it would be next best if I got the three data below
each other in one cell with Johnson in bold type.
If that also is not possible I will work with the three data below each
other.

I use Excel 2000 (9.0.6926 SP-3)

Your advice will be highly appreciated.

Jack Sons
The Netherlands



joel
combining 3 cells in 1 on different lines
 
You are combining strins and can do it using either the ampersand or the
concatenate function.

=A2&B2&K2

If you need spaces then add the using double quotes

=A2&" "&B2&" "&K2

If K2 is in the wrong format then use the TEXT function

=A2&B2&text(K2,"dd-mm-yy")



"Jack Sons" wrote:

Hi all,

In cell A2 is a name, say Johnson.
In cell B2 a code, say Ab89-w-5
In cell K2 is a date (in European notation) say 16 september 2009
All in Tmes New Roman 10.
Ik want in cell Q2 this

Johnson
Ab89-w-5
16 september 2009

So - on different lines - the name left aligned en both other data right
aligned (if possible)
Also if possible I would like the name in Times New Roman 10 and in bold,
and the rest in Arial 7 (not bold).

I spent a lot of time on it but I can't figure out what code to use and I
wonder if it is possible at all.
If all is not possible it would be next best if I got the three data below
each other in one cell with Johnson in bold type.
If that also is not possible I will work with the three data below each
other.

I use Excel 2000 (9.0.6926 SP-3)

Your advice will be highly appreciated.

Jack Sons
The Netherlands




Gord Dibben
combining 3 cells in 1 on different lines
 
=A2 & CHAR(10) & B2 & CHAR(10) & TEXT(K2, "dd mmmm yyyy")

Set wrap text and row and column autofit.

The fonts and bolding would have to be done through VBA

Here is some example code............play with it and see what you can come
up with.

Sub CellFont()
With ActiveCell.Characters(Start:=1, Length:=5).Font
.ColorIndex = 3
.Bold = True
.Underline = True
.Size = 14
End With
With ActiveCell.Characters(Start:=6, Length:=3).Font
.Superscript = True
.ColorIndex = 5
End With
With ActiveCell.Characters(Start:=10, Length:=4).Font
.Bold = True
.Size = 18
.ColorIndex = 6
End With
End Sub


Gord Dibben MS Excel MVP

On Sun, 30 Aug 2009 12:08:23 +0200, "Jack Sons" wrote:

Hi all,

In cell A2 is a name, say Johnson.
In cell B2 a code, say Ab89-w-5
In cell K2 is a date (in European notation) say 16 september 2009
All in Tmes New Roman 10.
Ik want in cell Q2 this

Johnson
Ab89-w-5
16 september 2009

So - on different lines - the name left aligned en both other data right
aligned (if possible)
Also if possible I would like the name in Times New Roman 10 and in bold,
and the rest in Arial 7 (not bold).

I spent a lot of time on it but I can't figure out what code to use and I
wonder if it is possible at all.
If all is not possible it would be next best if I got the three data below
each other in one cell with Johnson in bold type.
If that also is not possible I will work with the three data below each
other.

I use Excel 2000 (9.0.6926 SP-3)

Your advice will be highly appreciated.

Jack Sons
The Netherlands



Jack Sons
combining 3 cells in 1 on different lines
 
Joel and Gord,

Thank you both.

The formula I want to do in VBA, I know how.

But, Gord, as I already new, setting the cell content as you adviced will
result in everything in the cell following the characteristics of the first
part, so the whole content becomes red, bold, size 14 and underlined.
That really is my problem.

Any solution?

Jack.


"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
...
=A2 & CHAR(10) & B2 & CHAR(10) & TEXT(K2, "dd mmmm yyyy")

Set wrap text and row and column autofit.

The fonts and bolding would have to be done through VBA

Here is some example code............play with it and see what you can
come
up with.

Sub CellFont()
With ActiveCell.Characters(Start:=1, Length:=5).Font
.ColorIndex = 3
.Bold = True
.Underline = True
.Size = 14
End With
With ActiveCell.Characters(Start:=6, Length:=3).Font
.Superscript = True
.ColorIndex = 5
End With
With ActiveCell.Characters(Start:=10, Length:=4).Font
.Bold = True
.Size = 18
.ColorIndex = 6
End With
End Sub


Gord Dibben MS Excel MVP

On Sun, 30 Aug 2009 12:08:23 +0200, "Jack Sons" wrote:

Hi all,

In cell A2 is a name, say Johnson.
In cell B2 a code, say Ab89-w-5
In cell K2 is a date (in European notation) say 16 september 2009
All in Tmes New Roman 10.
Ik want in cell Q2 this

Johnson
Ab89-w-5
16 september 2009

So - on different lines - the name left aligned en both other data right
aligned (if possible)
Also if possible I would like the name in Times New Roman 10 and in bold,
and the rest in Arial 7 (not bold).

I spent a lot of time on it but I can't figure out what code to use and I
wonder if it is possible at all.
If all is not possible it would be next best if I got the three data below
each other in one cell with Johnson in bold type.
If that also is not possible I will work with the three data below each
other.

I use Excel 2000 (9.0.6926 SP-3)

Your advice will be highly appreciated.

Jack Sons
The Netherlands

eliano[_2_]
combining 3 cells in 1 on different lines
 
On 31 Ago, 00:05, "Jack Sons" wrote:
Joel and Gord,

Thank you both.

The formula I want to do in VBA, I know how.

But, Gord, as I already new, setting the cell content as you adviced will
result in everything in the cell following the characteristics of the first
part, so the whole content becomes red, bold, size 14 and underlined.
That really is my problem.

Any solution?

Jack.

"Gord Dibben" <gorddibbATshawDOTca schreef in berichtnews:255l95t6vsplv17cls4hp66u7tagbqmc83@4ax .com...



=A2 & CHAR(10) & B2 & CHAR(10) & TEXT(K2, "dd mmmm yyyy")

Set wrap text and row and column autofit.

The fonts and bolding would have to be done through VBA

Here is some example code............play with it and see what you can
come
up with.

Sub CellFont()
* *With ActiveCell.Characters(Start:=1, Length:=5).Font
* * * *.ColorIndex = 3
* * * *.Bold = True
* * * *.Underline = True
* * * *.Size = 14
* *End With
* *With ActiveCell.Characters(Start:=6, Length:=3).Font
* * * *.Superscript = True
* * * *.ColorIndex = 5
* *End With
* *With ActiveCell.Characters(Start:=10, Length:=4).Font
* * * *.Bold = True
* * * *.Size = 18
* * * *.ColorIndex = 6
* *End With
End Sub

Gord Dibben *MS Excel MVP

On Sun, 30 Aug 2009 12:08:23 +0200, "Jack Sons" wrote:

Hi all,

In cell A2 is a name, say Johnson.
In cell B2 *a code, say Ab89-w-5
In cell K2 is a date (in European notation) say 16 september 2009
All in Tmes New Roman 10.
Ik want in cell Q2 this

Johnson
* * * * * * * * *Ab89-w-5
* * 16 september 2009

So - on different lines - the name left aligned en both other data right
aligned (if possible)
Also if possible I would like the name in Times New Roman 10 and in bold,
and the rest in Arial 7 (not bold).

I spent a lot of time on it but I can't figure out what code to use and I
wonder if it is possible at all.
If all is not possible it would be next best if I got the three data below
each other in one cell with Johnson in bold type.
If that also is not possible I will work with the three data below each
other.

I use Excel 2000 (9.0.6926 SP-3)

Your advice will be highly appreciated.

Jack Sons
The Netherlands- Nascondi testo citato

- Mostra testo citato -

Hi Jack.

Gord said:
Here is some example code............play with it and see what you can
come
up with.

You can format values, not formula; so try:

Sub CellFont()
ActiveCell = ActiveCell.Value
With ActiveCell.Characters(Start:=1, Length:=7).Font
..ColorIndex = 3
..Bold = True
..Size = 14
End With
With ActiveCell.Characters(Start:=9, Length:=16).Font
..ColorIndex = 5
End With
With ActiveCell.Characters(Start:=18, Length:=17).Font
..Bold = True
..Size = 18
End With
End Sub

Regards
Eliano

Gord Dibben
combining 3 cells in 1 on different lines
 
Try this.............

Sub CellFont()
With ActiveSheet.Range("A1")
.Value = Range("A2").Value & " " & Chr(10) & _
Range("B2").Value & " " & Chr(10) & _
Format(Range("K2").Value, "dd mmmm yyyy")
With .Characters(Start:=1, Length:=Len(Range("A2").Value)).Font
.Bold = True
.Size = 10
.Name = "Times New Roman"
End With
With .Characters(Start:=Len(Range("A2").Value) + 2, _
Length:=Len((Range("B2").Value)) + (Range("K2").Value)).Font
'Length:= 100).Font
.Name = "Arial"
.Size = 7
End With
End With
End Sub


Gord


On Mon, 31 Aug 2009 00:05:24 +0200, "Jack Sons" wrote:

Joel and Gord,

Thank you both.

The formula I want to do in VBA, I know how.

But, Gord, as I already new, setting the cell content as you adviced will
result in everything in the cell following the characteristics of the first
part, so the whole content becomes red, bold, size 14 and underlined.
That really is my problem.

Any solution?

Jack.


"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
.. .
=A2 & CHAR(10) & B2 & CHAR(10) & TEXT(K2, "dd mmmm yyyy")

Set wrap text and row and column autofit.

The fonts and bolding would have to be done through VBA

Here is some example code............play with it and see what you can
come
up with.

Sub CellFont()
With ActiveCell.Characters(Start:=1, Length:=5).Font
.ColorIndex = 3
.Bold = True
.Underline = True
.Size = 14
End With
With ActiveCell.Characters(Start:=6, Length:=3).Font
.Superscript = True
.ColorIndex = 5
End With
With ActiveCell.Characters(Start:=10, Length:=4).Font
.Bold = True
.Size = 18
.ColorIndex = 6
End With
End Sub


Gord Dibben MS Excel MVP

On Sun, 30 Aug 2009 12:08:23 +0200, "Jack Sons" wrote:

Hi all,

In cell A2 is a name, say Johnson.
In cell B2 a code, say Ab89-w-5
In cell K2 is a date (in European notation) say 16 september 2009
All in Tmes New Roman 10.
Ik want in cell Q2 this

Johnson
Ab89-w-5
16 september 2009

So - on different lines - the name left aligned en both other data right
aligned (if possible)
Also if possible I would like the name in Times New Roman 10 and in bold,
and the rest in Arial 7 (not bold).

I spent a lot of time on it but I can't figure out what code to use and I
wonder if it is possible at all.
If all is not possible it would be next best if I got the three data below
each other in one cell with Johnson in bold type.
If that also is not possible I will work with the three data below each
other.

I use Excel 2000 (9.0.6926 SP-3)

Your advice will be highly appreciated.

Jack Sons
The Netherlands

Jack Sons
combining 3 cells in 1 on different lines
 
Gord and Eliano,

Thank you both, it worked very well.
Learned again something .

Jack.

"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
...
Try this.............

Sub CellFont()
With ActiveSheet.Range("A1")
.Value = Range("A2").Value & " " & Chr(10) & _
Range("B2").Value & " " & Chr(10) & _
Format(Range("K2").Value, "dd mmmm yyyy")
With .Characters(Start:=1, Length:=Len(Range("A2").Value)).Font
.Bold = True
.Size = 10
.Name = "Times New Roman"
End With
With .Characters(Start:=Len(Range("A2").Value) + 2, _
Length:=Len((Range("B2").Value)) + (Range("K2").Value)).Font
'Length:= 100).Font
.Name = "Arial"
.Size = 7
End With
End With
End Sub


Gord


On Mon, 31 Aug 2009 00:05:24 +0200, "Jack Sons" wrote:

Joel and Gord,

Thank you both.

The formula I want to do in VBA, I know how.

But, Gord, as I already new, setting the cell content as you adviced will
result in everything in the cell following the characteristics of the
first
part, so the whole content becomes red, bold, size 14 and underlined.
That really is my problem.

Any solution?

Jack.


"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
. ..
=A2 & CHAR(10) & B2 & CHAR(10) & TEXT(K2, "dd mmmm yyyy")

Set wrap text and row and column autofit.

The fonts and bolding would have to be done through VBA

Here is some example code............play with it and see what you can
come
up with.

Sub CellFont()
With ActiveCell.Characters(Start:=1, Length:=5).Font
.ColorIndex = 3
.Bold = True
.Underline = True
.Size = 14
End With
With ActiveCell.Characters(Start:=6, Length:=3).Font
.Superscript = True
.ColorIndex = 5
End With
With ActiveCell.Characters(Start:=10, Length:=4).Font
.Bold = True
.Size = 18
.ColorIndex = 6
End With
End Sub


Gord Dibben MS Excel MVP

On Sun, 30 Aug 2009 12:08:23 +0200, "Jack Sons"
wrote:

Hi all,

In cell A2 is a name, say Johnson.
In cell B2 a code, say Ab89-w-5
In cell K2 is a date (in European notation) say 16 september 2009
All in Tmes New Roman 10.
Ik want in cell Q2 this

Johnson
Ab89-w-5
16 september 2009

So - on different lines - the name left aligned en both other data right
aligned (if possible)
Also if possible I would like the name in Times New Roman 10 and in
bold,
and the rest in Arial 7 (not bold).

I spent a lot of time on it but I can't figure out what code to use and
I
wonder if it is possible at all.
If all is not possible it would be next best if I got the three data
below
each other in one cell with Johnson in bold type.
If that also is not possible I will work with the three data below each
other.

I use Excel 2000 (9.0.6926 SP-3)

Your advice will be highly appreciated.

Jack Sons
The Netherlands

eliano[_2_]
combining 3 cells in 1 on different lines
 
On 31 Ago, 11:10, "Jack Sons" wrote:
Gord andEliano,

Thank you both, it worked very well.
Learned again something .

Jack.

"Gord Dibben" <gorddibbATshawDOTca schreef in berichtnews:lavl95hna2n4qjes6eoq4v6m903ki4600s@4ax .com...



Try this.............

Sub CellFont()
* *With ActiveSheet.Range("A1")
* * * *.Value = Range("A2").Value & " " & Chr(10) & _
* * * * * * * * Range("B2").Value & " " & Chr(10) & _
* * * * * * * * Format(Range("K2").Value, "dd mmmm yyyy")
* * * *With .Characters(Start:=1, Length:=Len(Range("A2").Value)).Font
* * * * * *.Bold = True
* * * * * *.Size = 10
* * * * * *.Name = "Times New Roman"
* * * *End With
* * * *With .Characters(Start:=Len(Range("A2").Value) + 2, _
* * * * * *Length:=Len((Range("B2").Value)) + (Range("K2").Value)).Font
* * * * * *'Length:= 100).Font
* * * * * *.Name = "Arial"
* * * * * *.Size = 7
* * * *End With
* *End With
End Sub

Gord

On Mon, 31 Aug 2009 00:05:24 +0200, "Jack Sons" wrote:

Joel and Gord,

Thank you both.

The formula I want to do in VBA, I know how.

But, Gord, as I already new, setting the cell content as you adviced will
result in everything in the cell following the characteristics of the
first
part, so the whole content becomes red, bold, size 14 and underlined.
That really is my problem.

Any solution?

Jack.

"Gord Dibben" <gorddibbATshawDOTca schreef in bericht
. ..
=A2 & CHAR(10) & B2 & CHAR(10) & TEXT(K2, "dd mmmm yyyy")

Set wrap text and row and column autofit.

The fonts and bolding would have to be done through VBA

Here is some example code............play with it and see what you can
come
up with.

Sub CellFont()
* *With ActiveCell.Characters(Start:=1, Length:=5).Font
* * * *.ColorIndex = 3
* * * *.Bold = True
* * * *.Underline = True
* * * *.Size = 14
* *End With
* *With ActiveCell.Characters(Start:=6, Length:=3).Font
* * * *.Superscript = True
* * * *.ColorIndex = 5
* *End With
* *With ActiveCell.Characters(Start:=10, Length:=4).Font
* * * *.Bold = True
* * * *.Size = 18
* * * *.ColorIndex = 6
* *End With
End Sub

Gord Dibben *MS Excel MVP

On Sun, 30 Aug 2009 12:08:23 +0200, "Jack Sons"
wrote:

Hi all,

In cell A2 is a name, say Johnson.
In cell B2 *a code, say Ab89-w-5
In cell K2 is a date (in European notation) say 16 september 2009
All in Tmes New Roman 10.
Ik want in cell Q2 this

Johnson
* * * * * * * * *Ab89-w-5
* * 16 september 2009

So - on different lines - the name left aligned en both other data right
aligned (if possible)
Also if possible I would like the name in Times New Roman 10 and in
bold,
and the rest in Arial 7 (not bold).

I spent a lot of time on it but I can't figure out what code to use and
I
wonder if it is possible at all.
If all is not possible it would be next best if I got the three data
below
each other in one cell with Johnson in bold type.
If that also is not possible I will work with the three data below each
other.

I use Excel 2000 (9.0.6926 SP-3)

Your advice will be highly appreciated.

Jack Sons
The Netherlands- Nascondi testo citato

- Mostra testo citato -

Hi Jack.
Thanks to Gord; I've only played with his indications.
Regards
Eliano


All times are GMT +1. The time now is 02:31 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com