Formatting in formulas not allowed before commas

[Greg Lovern]

If I format a formula with carriage returns and spaces for clarity,
any carriage returns or spaces right before a comma are removed by
Excel. For example, if I do this:
(Yes, I realize this formula doesn't make sense; it's just a simple
example to illustrate the problem.)

=IF(
OR(
TRUE,TRUE
)
,TRUE,TRUE
)

As soon as press Enter, Excel changes it to this:

=IF(
OR(
TRUE,TRUE
),TRUE,TRUE
)

That happens with carriage returns right before commas, spaces right
before commas, and any combination of them right before commas.

Is there any way around that? The best I've been able to do is just do
the formatting after the comma instead of before it:

=IF(
OR(
TRUE,TRUE
),
TRUE,TRUE
)


Thanks,

Greg

 

[Fred Smith]

Sorry, Greg. I can't see the difference between your "before" and "after"
formulas. It will also help if you tell us what version of Excel you are
using.

As a general answer, Excel reformats all formulas to remove spaces and
change the function names and cell addresses to upper case.

Regards,
Fred

 

[Niek Otten]

<change the function names and cell addresses to upper case>

In fact Excel changes them to how they were defined. And Built-in functions
are always defined in uppercase.
But if you have a UDF that is defined in upper-and lowecase mix, Excel will
change it to match the definition, even if you type it in all lowercase.

 

[Niek Otten]

Not always, I just noticed. I'll try to find out when it does and when it
doesn't

 

[Niek Otten]

I remember again

In Excel5, VBA was still "a part of Excel"; it was very close to Excel and
the two exchanged more information. One such thing is the capitalization of
function names. That was lost in Excel97, when VBA became a more separate
component.
There were more such losses of functionality then.

Excel4 functions (XLM) still capitalize the way they were defined. They also
retain the ability for implicite intersection of multi-cell arguments, just
like Excel built-in functions do. But that's another story.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

 

[Greg Lovern]

Sorry, Greg. I can't see the difference between your "before" and "after"
formulas.

Hi Fred,

In the "before" formula, the IF function's 2nd and 3rd arguments are
on the next line below the OR function's closing parenthesis.

In the "after formula, the IF function's 2nd and 3rd arguments are on
the same line as the OR function's closing parenthesis -- Excel moved
them up a line, removing the carriage return and all spaces between
them.

Try this:

-- Copy the first formula.
-- Paste it into Notepad.
-- Paste it into an Excel cell and press Enter to evaluate it.
-- Copy it from the formula bar.
-- Paste it into Notepad, below what you already pasted into Notepad.
-- See the difference between the two formulas now in Notepad?

It will also help if you tell us what version of Excel you are
using.

I first noticed it in Excel 2007, then checked Excel 97 and found the
same behavior. I just now checked the Excel 2010 public beta and it
does the same. I haven't checked 2003, 2002, or 2000, but I would be
surprised if they were different.

As a general answer, Excel reformats all formulas to remove spaces

Not true. Try this:

= IF( TRUE, TRUE, TRUE )

There are 25 spaces in that formula. Excel accepts them, evaluates the
formula the same as it would without the spaces, and preserves the
spaces.

However, if you try to put a space between a function name and it's
opening parenthesis, Excel will remove all spaces in the formula.


Greg