Excel error

Check to see what the cell is formatted as - is it text, or a number? Does the information in it copy and paste into another program, such as Word, and show up correctly? Can you use the cell in a calculation, as in click an empty cell nearby and press the equal key, click the cell in question, press *2 and press enter? (Does it multiply the cell number by two?)

1. The cell is formatted as text, makes no difference.
2. This is the text copied hereunder
018-786-2902 32 Carletonville, Gauteng
3. I want to strip off that first number to read 0187862902
4. Multiply does not work but if I say =LEN(a1) it replies with 38
5. If I say =left(a1) it replies with "0"
6. If I say =left(a1,3) I get error.
It seems that if I add more than one parameter to the formula it gives the error.

can u plz attach the excel file?

I just opened a blank worksheet and typed in the above information into cell A1. I went to cell B1 and typed in "=left(a1)" and also got a 0 for a response. I went to cell B2 and typed in "=left(a1,3)" and it came back with 018 for a response. Can you try it with a blank spreadsheet and just type this in as I did, to see if the error reproduces?

It comes up with the same error.
This is Excel 2010
I go to another system with Excel 2007 and it works
So must be something in this version. Some default setting or OS environment. I checked the Regional setting but they are the same.

Test file that does not work if I add =left(a1,3) expecting 083 in a cell
test-file.xlsx

It worked for me - something is wrong on your computer or in your installation, not in your syntax or in your spreadsheet. I have updated the file with three entries like you are trying to make. Does it work when you open it?
test-file-after.xlsx

Hi,
No it does not.
This is very strange.

I'm afraid it may require the installation step I had mentioned above - do you have the media to reinstall it? You shouldn't need to uninstall it first or activate it later, just run the "change" part of the installation and use the custom setting to "Run all from my computer" - at least for Excel.

Also, you may want to run the Windows Updates - using the feature that allows updates to other programs from Microsoft. Office has several updates and one may fix this problem.

You may also check in Excel under File > Options > Add ins to see if any are disabled.

Also under Programs and Features, you may choose the Repair option for office, which may find the issue.

If it is an install problem then it would be consistent and would not work if I open another new file where it does work.

Nothing under options indicates a problem. Settings made same as a working spreadsheet.

Must be Excel bug.

To some extent you are correct, but things do not always work that way. The other troubleshooting step we tried was to try your file on another computer - mine - and the file worked fine. That contrasts with your observation and says it may be on your computer and a part of the installation.

The repair feature does not cause any issues and may solve it. Windows updates should not cause any issues in most cases. At least that's the theory. But, as Yogi Berra said, "In theory, things work the same in practice as they do in theory. But in practice they don't."

So do a full backup of your machine that you trust before taking any further steps. My favorite backup method is to use a second hard drive as an exact clone duplicate of the first hard drive, and then make changes to the backup drive. I personally use one of these hardware duplicators http://www.startech.com/HDD/Duplicators/Portable-eSATA-USB-to-SATA-Standalone-HDD-Hard-Drive-Duplicator-Dock~SATDUPUE that allows me to clone a hard drive to another of equal or greater size, without software or a PC, in a standalone environment. It's a more expensive option for backup, but it never fails and has saved me countless hours and let me back out of some big technical issues which were not easily solved, by putting things back where they were when I got there. Otherwise, if you make changes to a problem, it may become your problem, at least as a technician.

Point 1 taken. Must be some combination then because a fresh new file works.

I like that... yes in practice they don't ... and often the solution is so simple if one only knows how.

This system was hit by a virus from a client. So it is already reformatted. I will reformat again an reload as there is another unrelated problem and the two justify reloading.

I have a new notebook and will try it there as well. It has the same Office version (MAPS pack).

Will get back to you. Thanks so far.

That's the version I've used for years. Best of luck to you on it.

No real solution or answer as to why this happens