Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: length formula

  1. #1
    Board Regular hennahairgel's Avatar
    Join Date
    Feb 2002
    Location
    Bristol, England
    Posts
    63
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have some formula in cells which I from an output file, and I am using Excel to work with results.
    The problem is the output file generates results in the format of one letter and then five letters or numbers ie C12345 or F4567U. My problem comes when the first number is zero ie E09876, excel truncates it to E9876. I the #NAME? error by getting is to put everything as ="G45678", however I'm still stuck with the lost '0'.
    Is there a way of looking at the length of a formula in a cell, and if it is too short inserting a zero in a relevant position?
    TIA
    Henry

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-28 08:49, hennahairgel wrote:
    I have some formula in cells which I from an output file, and I am using Excel to work with results.
    The problem is the output file generates results in the format of one letter and then five letters or numbers ie C12345 or F4567U. My problem comes when the first number is zero ie E09876, excel truncates it to E9876. I the #NAME? error by getting is to put everything as ="G45678", however I'm still stuck with the lost '0'.
    Is there a way of looking at the length of a formula in a cell, and if it is too short inserting a zero in a relevant position?
    TIA
    Henry
    Evening Henry,

    I'm a bit confused with your description of the problem.

    the =g45678 is an instruction to tell Excel to read the cell in row 45678 intersected by column G

    also, Excel actually truncates E09876 to E9876 ? I may be a million miles away but it sounds like it's trying to interpret this as a scientific format (ie 1.23E89, itself a big number) where nE9876 would be a number bigger than Imelda Marcos's total shoe inventory (!)
    :: Pharma Z - Family drugstore ::

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Chippenham, UK
    Posts
    144
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-28 08:49, hennahairgel wrote:
    I have some formula in cells which I from an output file, and I am using Excel to work with results.
    The problem is the output file generates results in the format of one letter and then five letters or numbers ie C12345 or F4567U. My problem comes when the first number is zero ie E09876, excel truncates it to E9876. I the #NAME? error by getting is to put everything as ="G45678", however I'm still stuck with the lost '0'.
    Is there a way of looking at the length of a formula in a cell, and if it is too short inserting a zero in a relevant position?
    TIA
    Henry
    =IF(LEN(A1)=6,CONCATENATE(MID(A1,1,1),"0",MID(A1,2,6)),A1)

    If the data is in cell A1, that is if the length of the string is always 6 without the 0 and 7 with the 0. If it is different the formula will need modifying.




    _________________
    Regards,

    Gary Hewitt-Long

    [ This Message was edited by: gplhl on 2002-02-28 11:40 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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