Carriage return in formula
Marc Bouts
I would like to include a Carriage Return in en formula. I thought it had to
look someting like this :
if (A1 = "Yes" ; "This is the first line" & "ś" & "this is the second line"
; "")
But excel doesn't seem tot recognize "ś".
I already tried chr (10) but is doesn't work.
Does anyone knows how to resolve this problem?
Thank you in advance,
Marc
David McRitchie
You want to use CHAR(10) in a worksheet formula.
=if (A1 = "Yes" ; "This is the first line" & CHR(10) & "this is the second line")
When you enter directly into a cell and use Alt+Enter to split
the line within a cell you get an automatic Wrap. If the cell is
not already wrapped you want to format the cell
Format, cells, alignment (tab), wrap text
It is more efficient to format the entire column if that is what you
actually want.
FWIW, you use CHR(10) in VBA.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Marc Bouts" <ma...@bestdeal.be> wrote in message news:3ec8c3c4$0$11150$ba62...@reader0.news.skynet.be...
Marc Bouts
This is not a solution.
The whole idea is to do the following :
Let's say I have following cels, with following contents :
A1 = This
A2 = is
A3 =
A4 =
A5 = a
A6 = list
Now I want formula in A10 (& "¶" = in this case is the syntax for a carriage
return or ALT + Return) :
if (A1 <> "" ; A1 & "¶" ; if (A2 <> "" ; A2 & "¶" ; if (A3 <> "" ; A3 & "¶"
; if (A4 <> "" ; A4 & "¶" ; if (A5 <> "" ; A5 & "¶" ; if (A6 <> "" ; A6 &
"¶"))))))
This should give me in A10 :
This
is
a
list
The whole idea is to exclude line if the corresponding cell is empty.
I hope this makes things clearer.
Thank you,
Marc
> > if (A1 = "Yes" ; "This is the first line" & "¶" & "this is the second
line"
> > ; "")
> >
> > But excel doesn't seem tot recognize "¶".
Bill Manville
> Now I want formula in A10 (& "¶" = in this case is the syntax for a carriage
> return or ALT + Return) :
>
> if (A1 <> "" ; A1 & "¶" ; if (A2 <> "" ; A2 & "¶" ; if (A3 <> "" ; A3 & "¶"
> ; if (A4 <> "" ; A4 & "¶" ; if (A5 <> "" ; A5 & "¶" ; if (A6 <> "" ; A6 &
> "¶"))))))
>
=IF(A1 <> "" ; A1 & CHAR(10); "") & IF(A2 <> "" ; A2 & CHAR(10) ; "") & IF (A3
<> "" ; A3 & CHAR(10) ; "") & IF(A4 <> "" ; A4 & CHAR(10) ; "") & IF(A5 <> ""
; A5 & CHAR(10) ; "") & IF(A6 <> "" ; A6 & CHAR(10))
Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup
Marc Bouts
version of excel and I should use a translation of CHAR. the problem is that
I just don't know what!
Can somebody help me,
thank you,
Marc
"Bill Manville" <Bill-M...@msn.com> schreef in bericht
news:VA.0000103...@msn.com...
David McRitchie
Why not try Bill's solution a piece at a time.
You will have to have the entire thing on one line.
You would know if your Dutch version uses commas or semi-colons,
Bill repeated back your use of semi-colons and he probably knows
what you use anyway. Everything is null string (empty)
surrounded by double quotes. No less than or greater than, so
watch out for the reply indicators
everything tested for is a test for not equal <>
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Marc Bouts" <ma...@bestdeal.be> wrote in message news:3ec9192b$0$11130$ba62...@reader0.news.skynet.be...
> thank you, but it doesn't work. Maybe the reason is that I have a dutch
> version of excel and I should use a translation of CHAR. the problem is that
> I just don't know what!
> Can somebody help me,
>
> thank you,
>
> Marc
> "Bill Manville" <Bill-M...@msn.com> schreef in bericht
> news:VA.0000103...@msn.com...
> > Marc Bouts wrote:
> > > Now I want formula in A10 (& "ś" = in this case is the syntax for a
> carriage
> > > return or ALT + Return) :
> > >
> > > if (A1 <> "" ; A1 & "ś" ; if (A2 <> "" ; A2 & "ś" ; if (A3 <> "" ; A3 &
> "ś"
> > > ; if (A4 <> "" ; A4 & "ś" ; if (A5 <> "" ; A5 & "ś" ; if (A6 <> "" ; A6
> &
> > > "ś"))))))
David McRitchie
You will have to have the entire thing on one line.
You would know if your Dutch version uses commas or semi-colons,
Bill repeated back your use of semi-colons and he probably knows
what you use anyway. Everything is null string (empty)
surrounded by double quotes. No less than or greater than, so
watch out for the reply indicators
everything tested for is a test for not equal <>
=IF(A1 <> "" ; A1 & CHAR(10); "") & IF(A2 <> "" ; A2 & CHAR(10) ; "") & IF (A3 <> "" ; A3 & CHAR(10) ; "") & IF(A4 <> "" ; A4 &
CHAR(10) ; "") & IF(A5 <> ""; A5 & CHAR(10) ; "") & IF(A6 <> "" ; A6 & CHAR(10))
I tested with my US version using commas
=IF(A1 <> "" A1 & CHAR(10), "") & IF(A2 <> "" , A2 & CHAR(10), "") & IF (A3 <> "" , A3 & CHAR(10) , "") & IF(A4 <> "" , A4 &
CHAR(10), "") & IF(A5 <> "", A5 & CHAR(10) , "") & IF(A6 <> "" , A6 & CHAR(10))
You have to prepare the line in notepad to remove the linebreaks introduced by posting
to newsgroup. Though they may not be present when I send this -- I don't know.
I do know that you will have linebreaks from most people posting if a line will not
fit.
I think this is same as above, anyway this worked for me, you have to turn on cell wrapping
otherwise you will see the square hollow bullets for the CHAR(10)
=IF(A1<>"",A1&CHAR(10),"")&IF(A2<>"",A2&CHAR(10),"")&IF(A3<>"",A3&CHAR(10),"")&IF(A4<>"",A4&CHAR(10),"")&IF(A5<>"",A5&CHAR(10),"")&I
F(A6<>"",A6&CHAR(10))
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Marc Bouts" <ma...@bestdeal.be> wrote in message news:3ec9192b$0$11130$ba62...@reader0.news.skynet.be...
> thank you, but it doesn't work. Maybe the reason is that I have a dutch
> version of excel and I should use a translation of CHAR. the problem is that
> I just don't know what!
> Can somebody help me,
>
> thank you,
>
> Marc
> "Bill Manville" <Bill-M...@msn.com> schreef in bericht
> news:VA.0000103...@msn.com...
> > Marc Bouts wrote:
> > > Now I want formula in A10 (& "ś" = in this case is the syntax for a
> carriage
> > > return or ALT + Return) :
> > >
> > > if (A1 <> "" ; A1 & "ś" ; if (A2 <> "" ; A2 & "ś" ; if (A3 <> "" ; A3 &
> "ś"
> > > ; if (A4 <> "" ; A4 & "ś" ; if (A5 <> "" ; A5 & "ś" ; if (A6 <> "" ; A6
> &
> > > "ś"))))))
Bill Manville
> Maybe the reason is that I have a dutch
> version of excel and I should use a translation of CHAR. the problem is that
> I just don't know what!
>
TEKEN
Eric van Uden
As this is an English-spoken newsgroup, I prefer to keep to that language,
but if you are still having trouble after this, mail to me directly and I
will try to help you in Dutch, off-group.
David and Bill gave you the correct answer, which is what they are so famous
for, of course ;^)
But to help you along some more, here is the formula in 'Dutch':
=ALS(A1<>"";A1&TEKEN(10);"")&ALS(A2<>"";A2&TEKEN(10);"")&ALS(A3<>"";A3&TEKEN
(10);"")&ALS(A4<>"";A4&TEKEN(10);"")&ALS(A5<>"";A5&TEKEN(10);"")&ALS(A6<>"";
A6&TEKEN(10);"")
equals
=IF(A1<>"",A1&CHAR(10),"")&IF(A2<>"",A2&CHAR(10),"")&IF(A3<>"",A3&CHAR(10),"
")&IF(A4<>"",A4&CHAR(10),"")&IF(A5<>"",A5&CHAR(10),"")&IF(A6<>"",A6&CHAR(10)
,"")
(With the slightest correction at the very end of Bill's formula, where the
second argument of the IF-statement apperently took an early holiday... )
You probably have this by now. I tested it and it does what you asked for.
Substitute ALS for IF and TEKEN for CHAR. Then, in Dutch Excel, we separate
with a semicolon (;), not a comma (,). And you're translated.
Remember what David said: if you copy a long formula from the web (or any
place other than an Excel spreasheet, for that matter), things may have
altered. Specifically, linebreaks ("enter's") may have been added by a
wordprocessor, or right-acute-brackets (">") may have been added by an
e-mail client to indicate a quoted text.
In Excel, though, those would have a different meaning and corrupt the
formula. So copying the formula to notepad ('kladblok') first, to clean it
up, is good advise. Or even try copying it to paper and type it into Excel
by hand.
As I copied and pasted Bill's formula, I couldn't get it to work either. I
am no expert, but as formulae come, it is quite straightforward, and nothing
looked wrong as I read it. However, oncloser inspection I found that spaces
and linebreaks had crept in. Furthermore the 'European' semicolons weren't
accepted by my favorite translation tool FFTranslate, which works with a
pure American Excel and demands commas.
There are several places you can go to translate a formula to or from
English. In FFTranslate you can paste a whole formula, and it will be
translated in full. The other solutions are mostly lists of terms, with
their translations:
http://www.downloadcounter.com/cgi-bin/download.pl?username=ivanm&account=13
for the FFTranslate addin, by XL-Dennis from Sweden,
http://www.microsoft.com/office/ork/2000/appndx/toolbox.htm#cmdtrans for
Microsoft's Office 2000 Command Translator, which translates menus and
toolbars (you will need Access 2000), American craftsmanship,
http://www.acoustics-noise.com/ for Analysis Toolpak Translator by Eric
Desart from Belgium,
http://users.pandora.be/frankvanbrabandt/Vertalingen/, another Belgian
effort,
http://cherbe.free.fr/traduc_fonctions_xl97.html for Excel funtions side by
side (9 languages), a French contribution
http://members.chello.nl/keepitcool/files/MultiFormulaTranslator.zip,
MultiFormula Command Translator from Dutchman J.Volkerink,
http://www.bmsltd.co.uk/DLCount/DLCount.asp?file=xlMenuFunDict.zip, from Jan
Karel Pieterse, with translations for formula items, command bar items and
about anything else. As Dutch as a wooden shoe.
A quick and easy solution may also be provided by your system: I find that
when I enter a search for an English function-name in the Help-box of my
Office XP Excel, it leads me to the correct subject as it translates to
Dutch.
Furthermore you will find that an English spreadsheet translates to Dutch
automatically (as far as formulae in non-blocked cells are concerned) when
opened in Dutch Excel.
Have a great day,
Eric van Uden
at the foot of the 'bridge too far'
Eric van Uden
microsoft.public.nl.office.excel.