+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    09-13-2012
    Location
    Malta
    MS-Off Ver
    Excel 2007
    Posts
    29

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

    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?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,108

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

    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?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    09-13-2012
    Location
    Malta
    MS-Off Ver
    Excel 2007
    Posts
    29

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

    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**!
    Last edited by Duoae; 11-19-2012 at 03:37 PM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,108

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

    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.

  5. #5
    Registered User
    Join Date
    09-13-2012
    Location
    Malta
    MS-Off Ver
    Excel 2007
    Posts
    29

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

    Example worksheet for forum.xlsx

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

  6. #6
    Registered User
    Join Date
    09-13-2012
    Location
    Malta
    MS-Off Ver
    Excel 2007
    Posts
    29

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

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

    Please Login or Register  to view this content.
    And this formula to pick out the percentage reported:

    Please Login or Register  to view this content.
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1