Functions will not enter - Left, Find, If

[dataspot21]

I am trying to enter formulas I have used for years, but I continue to get the general error message:

The formula you typed contains an error.
For information about fixing common formula problems, click Help
To get assistance in entering a function, clcik Function Wizard (Formulas tab, Function Library group)
If you are not trying to enter a formula, avoid using an equal sign (=), or preced it with a single quoatation mark (')

I get this message when trying to enter any of these formulas: Left, Right, mid, Find, If. However, I can enter the Upper formula, referencing cell a2, which I am referencing when I try to use the other formulas.

Column a, containing full name data (LN, FN) is formatted as General. Column b, where I am entering the formulas, is formatted as General. In my File Options, automatic calculation is checked.

I would appreciate suggestions for resolution. FYI - I am using Excel 2010 on Windows 8.1 laptop.

Thank you

 

[Jonmo1]

Welcome to the board.

Is it any formula that requires a comma seperator (you listed left right mid find and if)
The only one you listed that you CAN enter does not require the comma seperator (Upper).

Perhaps you need to use ; instead of , in the formula (or vice versa)

 

[dataspot21]

Thanks for the welcome. Here is waht my data looks likes. Column a has full name as LN, FN

A1: Smith, John
In B1, I type =left(a1,5)

I plan to change the formula to use FInd to find the location of the comma and use that instaead of 5.

But cannot even get the string 'Smith' to return from =left(a1,5)

Welcome to the board.

Is it any formula that requires a comma seperator (you listed left right mid find and if)
The only one you listed that you CAN enter does not require the comma seperator (Upper).

Perhaps you need to use ; instead of , in the formula (or vice versa)

 

[Jonmo1]

So when you enter
=left(a1,5)
You get the generic error "the formula you entered contains an error..."
Right?

How about
=left(a1;5)

 

[dataspot21]

Same error when typing =left(a1;5)

 

[Rick Rothstein]

If you open a new workbook and try that same formula in it, do you still get the same error message?

 

[Jonmo1]

What about just

=LEFT(A1)

I know you want 5, but this is just for troubleshooting..

 

[dataspot21]

In a new workbook:

=Left(a1) returns S
=Left(a1;5) returns same error


[TABLE="width: 165"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Smith, John[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smith, John[/TD]
[TD]S[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

=Left(a1) returns S
=Left(a1;5) returns same error

That pretty much confirms it.
The problem MUST be with the list seperator character (character used between arguments in formulas)
It's usually a comma or semicolon.
But it can actually be pretty much anything
And is determined in your Windows Control Panel - Regional Settings - Additional Settings - List Seperator

 

[dataspot21]

Thanks Jonmo1. I forgot that I had been experimenting with creating pipe-delimited text files last week. Seems I forgot to change the seetings back to comma. Thanks much for your help.