MID function: +0 modifier converting % symbol into part of the number

**Duoae** · 11-19-2012, 01:02 PM

Evening all!

Bit more of a question than a real problem, really. I'm using the MID function to return part of a cell as a number and specifying how many characters I want it to print. Now, the layout of the cell is ideally something like this:

X.XXX% (X.XXX)

Where X is any number; 0-9.

Unfortunately, silly users are inputting data in forms such as:

X.XXX (X.XXX), X.XX% (X.XXX), X.XXX(X.XX)% etc. etc.

So I was trying to be clever and predict some of these variations in my formula and work around them. Unfortunately, I noticed that when it encounters a % sign it then converts it into a number. So the cell:

1.2% (0.123)

Would print as:

0.012

Does anyone know if there is any way around this behaviour? I guess this is intended functionality on the part of Excel 2007 but I don't know what the +0 modifier is called so my google searching is pulling up blanks.

The only two options I can think of now is to go back to the previous "non-smart" method whereby I was only accepting the X.XXX% (X.XXX) format.... OR to convolute the process and detect % signs and then either remove them or work around them.

What do you all think?

**FDibbins** · 11-19-2012, 01:23 PM

perhaps the best way to fix that, is to prevent it in the 1st place. have you tried to use data validation for force the users to enter data correctly?

**Duoae** · 11-19-2012, 03:32 PM

Unfortunately, the data I'm referencing is managed by a different department and they are reluctant to go back through 5+ years of data and make sure it's all in the right format before they enable the data validation on those cells.

They've agreed to do it for new projects (which of course won't have any data for a large portion of their initial life cycle) and in cases that my users bring to them where they need things updating (i.e. on a case-by-case basis... which is very inconvenient for both departments, IMO) but otherwise we're a bit stuck - which is why I'm trying to work around these things.

All of this essentially means we have to do stuff manually. Which is a huge pain in the a**!

**FDibbins** · 11-19-2012, 03:36 PM

Ok i understand. can you make up a sample workbook showing a few different samples of the incorrect data, and what it should have looked like please? nothing elaborate, just as long as it shows what you are working with

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

**Duoae** · 11-20-2012, 03:38 AM

Example worksheet for forum.xlsx

There! I hope that helps clarify things - took me a while to put together.

**Duoae** · 11-21-2012, 05:30 AM

I managed to work around most of the problems myself. Using this formula to pick the data between the brackets:

IF(ISNUMBER(SEARCH("(",C33)),IF((SEARCH(")",C33))-(SEARCH("(",C33))=6,MID(C33,SEARCH("(",C33)+1,5)+0,IF((SEARCH(")",C33))-(SEARCH("(",C33))=5,MID(C33,SEARCH("(",C33)+1,4)+0,IF((SEARCH(")",C33))-(SEARCH("(",C33))=4,MID(C33,SEARCH("(",C33)+1,3)+0,IF((SEARCH(")",C33))-(SEARCH("(",C33))>6,MID(C33,SEARCH("(",C33)+1,5)+0," "))))," ")

And this formula to pick out the percentage reported:

IF(AND($F:$F>=$J$15,$F:$F<$J$16),IF(SEARCH("(",$B:$B)=8,MID($B:$B,SEARCH("(",$B:$B)-7,5)+0,IF(AND(SEARCH("(",$B:$B)=7,SEARCH("%",$B:$B)=6),MID($B:$B,SEARCH("(",$B:$B)-6,5)+0,IF(AND(SEARCH("(",$B:$B)=7,SEARCH("%",$B:$B)=5),MID($B:$B,SEARCH("(",$B:$B)-6,4)+0,IF(AND(SEARCH("(",$B:$B)=7,SEARCH("%",$B:$B)>7),MID($B:$B,SEARCH("(",$B:$B)-6,5)+0,IF(AND(SEARCH("(",$B:$B)=6,SEARCH("%",$B:$B)=5),MID($B:$B,SEARCH("(",$B:$B)-5,4)+0,IF(AND(SEARCH("(",$B:$B)=6,SEARCH("%",$B:$B)=4),MID($B:$B,SEARCH("(",$B:$B)-5,3)+0,IF(AND(SEARCH("(",$B:$B)=6,SEARCH("%",$B:$B)>6),MID($B:$B,SEARCH("(",$B:$B)-5,5)+0," ")))))))," ")

I basically made an exception for all (or at least most) possible combinations of mistakes I could think of. The only exception being where someone forgot to add in a number between the brackets, close the brackets or have a word before either the percentage reported or the number in the brackets.

I posted the formulas in case they're of help to anyone in the future.