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?