|
|
| |
 Question |
|
| |
For example: =concatenate(a1,a2,a3,a4) = ILIKEBEEFBURGERS, But I want it to
put the result into a single excel cell like this:
I
LIKE
BEEF
BURGERS
But i cant see how to manually insert a line break into my statement as in
something like:
=concatenate(a1,<line break>,a2,<line break>,a3,<line break>,a4,<line break>,)
Any suggestions would be appreciated, thanks :P |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
| |
 Answer |
|
| |
=CONCATENATE(A1,CHAR(10),A2,CHAR(10),A3,CHAR(10),A4)
or
=A1 & CHAR(10) & A2 & CHAR(10) & A3 & CHAR(10) & A4
Make sure the cell is formatted to wrap text.
"Bretter99" wrote:
> For example: =concatenate(a1,a2,a3,a4) = ILIKEBEEFBURGERS, But I want it to
> put the result into a single excel cell like this:
> I
> LIKE
> BEEF
> BURGERS
>
> But i cant see how to manually insert a line break into my statement as in
> something like:
> =concatenate(a1,<line break>,a2,<line break>,a3,<line break>,a4,<line break>,)
> Any suggestions would be appreciated, thanks :P |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Hi there - thanx for this ... it reallly helped me. Since your formula
assisted me once, perhaps you could help me with this.
This formula works but I would like to tell the formula to omit lines or
that are blank.
i.e.
=CONCATENATE(I15,CHAR(10),J15,CHAR(10),K15,CHAR(10),L15,CHAR(10),M15,CHAR(10),N15,CHAR(10),O15)
Say line K15, L15 and M15 had no data like this :
"227 Finney Lane
Heald Green
Cheadle
SK8 3QB"
How can I tell it to omit the blank lines?
Your help would be much appreciated.
Thanx
"JLatham" wrote:
> =CONCATENATE(A1,CHAR(10),A2,CHAR(10),A3,CHAR(10),A4)
> or
> =A1 & CHAR(10) & A2 & CHAR(10) & A3 & CHAR(10) & A4
>
> Make sure the cell is formatted to wrap text.
>
> "Bretter99" wrote:
>
> > For example: =concatenate(a1,a2,a3,a4) = ILIKEBEEFBURGERS, But I want it to
> > put the result into a single excel cell like this:
> > I
> > LIKE
> > BEEF
> > BURGERS
> >
> > But i cant see how to manually insert a line break into my statement as in
> > something like:
> > =concatenate(a1,<line break>,a2,<line break>,a3,<line break>,a4,<line break>,)
> > Any suggestions would be appreciated, thanks :P |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Function ConCatRange(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & Chr(10)
Next
ConCatRange = Left(sbuf, Len(sbuf) - 1)
End Function
In a cell enter =ConCatRange(I15:O15)
The blank cells will be ignored.
Format the cell to "Wrap Text"
Gord Dibben MS Excel MVP
On Fri, 5 Jun 2009 19:26:02 -0700, AfricanTigress
<AfricanTigress@discussions.microsoft.com> wrote:
>Hi there - thanx for this ... it reallly helped me. Since your formula
>assisted me once, perhaps you could help me with this.
>
>This formula works but I would like to tell the formula to omit lines or
>that are blank.
>
>i.e.
>=CONCATENATE(I15,CHAR(10),J15,CHAR(10),K15,CHAR(10),L15,CHAR(10),M15,CHAR(10),N15,CHAR(10),O15)
>
>Say line K15, L15 and M15 had no data like this :
>
>"227 Finney Lane
>Heald Green
>
>
>
>Cheadle
>SK8 3QB"
>
>How can I tell it to omit the blank lines?
>
>Your help would be much appreciated.
>
>Thanx
>
>
>
>"JLatham" wrote:
>
>> =CONCATENATE(A1,CHAR(10),A2,CHAR(10),A3,CHAR(10),A4)
>> or
>> =A1 & CHAR(10) & A2 & CHAR(10) & A3 & CHAR(10) & A4
>>
>> Make sure the cell is formatted to wrap text.
>>
>> "Bretter99" wrote:
>>
>> > For example: =concatenate(a1,a2,a3,a4) = ILIKEBEEFBURGERS, But I want it to
>> > put the result into a single excel cell like this:
>> > I
>> > LIKE
>> > BEEF
>> > BURGERS
>> >
>> > But i cant see how to manually insert a line break into my statement as in
>> > something like:
>> > =concatenate(a1,<line break>,a2,<line break>,a3,<line break>,a4,<line break>,)
>> > Any suggestions would be appreciated, thanks :P
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
| |
Answer |
|
| |
Hi
Try
=A1&CHAR(10)&A2&CHAR(10)&A3&CHAR(10)&A4
Set the format of the cell>alignment>Wrap text
--
Regards
Roger Govier
"Bretter99" <Bretter99@discussions.microsoft.com> wrote in message
news:4EDF9D6C-6569-4774-9640-D2275DA71AE9@microsoft.com...
> For example: =concatenate(a1,a2,a3,a4) = ILIKEBEEFBURGERS, But I want
> it to
> put the result into a single excel cell like this:
> I
> LIKE
> BEEF
> BURGERS
>
> But i cant see how to manually insert a line break into my statement
> as in
> something like:
> =concatenate(a1,<line break>,a2,<line break>,a3,<line break>,a4,<line
> break>,)
> Any suggestions would be appreciated, thanks :P
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
I am creating a file with sqr. A chr(10) --- which is supposed to be a line
feed --- not only does a line feed but also a carriage return. How do I set
the format of a cell in an sqr?
"Roger Govier" wrote:
> Hi
>
> Try
> =A1&CHAR(10)&A2&CHAR(10)&A3&CHAR(10)&A4
> Set the format of the cell>alignment>Wrap text
>
> --
> Regards
>
> Roger Govier
>
>
> "Bretter99" <Bretter99@discussions.microsoft.com> wrote in message
> news:4EDF9D6C-6569-4774-9640-D2275DA71AE9@microsoft.com...
> > For example: =concatenate(a1,a2,a3,a4) = ILIKEBEEFBURGERS, But I want
> > it to
> > put the result into a single excel cell like this:
> > I
> > LIKE
> > BEEF
> > BURGERS
> >
> > But i cant see how to manually insert a line break into my statement
> > as in
> > something like:
> > =concatenate(a1,<line break>,a2,<line break>,a3,<line break>,a4,<line
> > break>,)
> > Any suggestions would be appreciated, thanks :P
>
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
Notify me of replies