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:
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:
Would print as:
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?