Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello I am trying to write a formula in Excel 2007. I want to be able to look at a cell and look at several "logic" items.

    If the cell begins with an alpha character (the cell values are alpha-numeric) I want to return that cell value.
    If the cell DOES NOT begin with an alpha character I want it to return a blank value "".
    If the cell is blank I want it to return a blank value "".

    I have the blank issue down with the following formula =If(E3="","",E3) I don't know how to rewrite it to look for an alpha character and return that value.

    Thanks

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Y,

    Try this: =IF(ISBLANK(C3),"",IF(ISTEXT(C3),C3,"")) of course substituting E's for my C's.

    You have to evaluate for blank first because a blank will evaluate at text and return a zero if you check for ISTEXT first!

    RG
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I think the preceding zeros are causing the issue. Without the zeros the formula works but I need to keep the cell value as is. Is there a way to ignore the zeros without getting rid of them? For example my cell values are SF00-PAY100 and 0000123499. I want it to return only SF00-PAY100 but it is returning 0000123499 also.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Y,

    Ok, I think your problem is your numbers e.g. 0000123499 are stored as Text not Numbers.

    One way around this, in which my original solution works, is to use a custom number format of "0000000000" then enter your numbers as numbers, they will still display with the leading zeros but will also evaluate as numbers not text (see attached file). If you are importing your data you may need some VBA to convert these numbers represented as text into real numbers.

    The other solution is to check for "SF" as the 1st 2 characters of a cell with Left(C3,2) = "SF". Of course, I can't tell if this will work since I can't see your entire data set. If it is possible a post of your spreadsheet, or cleaned up subset of it, would be most useful in a full analysis of your problem.

    RG
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have attached a portion of the spreadsheet I am working on. I can't have it look specifically for SF. This file is to be used by several different "companies". Some may have a prefix of AG, GL, MA, etc. It changes depending on the user. Columns A - F come from a report. Column K is the one that houses the formula in question. I just want the formula to pull back anything tha tbegins with a letter. Also, with the custom 0000000 for the zeros, would that affect the remaining data in the column or would I have to manually update each cell?

    I basically would like the user to place their report in the cells and then based on the formula results they would hit a button that houses a macro and the data would be placed in the appropriate columns on a different worksheet that is not included.

    Thanks for your assistance.

  6. #6
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello - This formula seems to do what is wanted.
    =IF(ISERROR(VALUE(LEFT(E3,1))),T(E3),"")

    See attached spreadsheet.

    Tim


    Quote Originally Posted by yjones72 View Post
    I have attached a portion of the spreadsheet I am working on.
    PS: There was no sample sheet attached to yjones72 last post.
    Attached Files Attached Files

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Y,

    Ok, with those parameters try: =IF(ISBLANK(C1),"",IF(AND(LEFT(C1,1)>="A",LEFT(C1, 1)<="z"),C1,""))

    See attached workbook.

    RG
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    Star Lounger
    Join Date
    Jun 2005
    Location
    Delaware
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you both so much. This helped. :-)

Posting Permissions

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