• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Excel error

Copied some data from pdf document into excel.
Now I want to extract a phone number in the first 10 characters.
So I go =left(a1,10) but it comes up with an error
the message is meaningless as it just says error.
If I just go =left(a1) it extracts the first character as it should.
0
Alex Angus
Asked:
Alex Angus
  • 8
  • 7
1 Solution
 
Norm DickinsonGuruCommented:
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?)
0
 
Alex AngusDirectorAuthor Commented:
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.
0
 
nam0aCommented:
can u plz attach the excel file?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Norm DickinsonGuruCommented:
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?
0
 
Alex AngusDirectorAuthor Commented:
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.
0
 
Norm DickinsonGuruCommented:
I am also using Excel 2010. I manually typed in the data you had copied and it worked like it was supposed to.

When I installed Microsoft Office, instead of default settings, I chose "Run All from My Computer" in the installation options menu. You may need to do that. You can do it from the add/remove under Control Panel / Programs and Features applet or by inserting the installation disc again - something may be missing from the installation that you need.
0
 
Alex AngusDirectorAuthor Commented:
Test file that does not work if I add =left(a1,3) expecting 083 in a cell
test-file.xlsx
0
 
Norm DickinsonGuruCommented:
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
0
 
Alex AngusDirectorAuthor Commented:
Hi,
No it does not.
This is very strange.
0
 
Norm DickinsonGuruCommented:
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.
0
 
Norm DickinsonGuruCommented:
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.
0
 
Alex AngusDirectorAuthor Commented:
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.
0
 
Norm DickinsonGuruCommented:
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.
0
 
Alex AngusDirectorAuthor Commented:
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.
0
 
Norm DickinsonGuruCommented:
That's the version I've used for years. Best of luck to you on it.
0
 
Alex AngusDirectorAuthor Commented:
No real solution or answer as to why this happens
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now