+ Reply to Thread
Results 1 to 8 of 8

#Value Error when Executing Formula

  1. #1
    Registered User
    Join Date
    01-09-2012
    Location
    Huntsville, Alabama
    MS-Off Ver
    Excel 2007
    Posts
    29

    #Value Error when Executing Formula

    Greetings to all,

    I keep getting a #Value error when I attempt to execute the following formula and I was hoping someone could point out my mistake.

    Please Login or Register  to view this content.
    Cell A2 contains: "* 423" (no quotes, and that first character is an asterik)

    There must be something that I am over looking but for the life of me I cannot see it, which is why I was hoping other sets of eyes will spot my mistake immediately.

    Thanks to all, and I hope everyone has a great day.

  2. #2
    Registered User
    Join Date
    04-14-2013
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    66

    Re: #Value Error when Executing Formula

    I am not positive about this, but I think that * is uesed as some sort of string operator. If my memory serves me correctly it has something to do with using part of a text string. Not 100% on that but if it is then that could be part of your problem.
    If you found this helpful, click the star

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: #Value Error when Executing Formula

    Don't double up the quotes in a formula...That's only in VBA..
    =IF(AND(LEFT(A2,1)="*",MID(A2,2,1)<>"*"),A2,D1)

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: #Value Error when Executing Formula

    Try it like this...

    =IF(AND(LEFT(A2)="*",MID(A2,2,1)<>"*"),A2,D1)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    05-09-2009
    Location
    Manchester,England
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    58

    Re: #Value Error when Executing Formula

    Hi,

    you have double speech marks

    see below

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: #Value Error when Executing Formula

    Perhaps
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-09-2012
    Location
    Huntsville, Alabama
    MS-Off Ver
    Excel 2007
    Posts
    29

    Thumbs up Re: #Value Error when Executing Formula

    Hey,

    Thanks to all who responded, it was the double quotes and also the way the cells were formatted, so a double whammy. But it works now, so thanks to all.

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: #Value Error when Executing Formula

    Good deal. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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