+ Reply to Thread
Results 1 to 8 of 8

Using "IF" formula on text not converted to a number

  1. #1
    Registered User
    Join Date
    08-13-2013
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2013
    Posts
    58

    Using "IF" formula on text not converted to a number

    I'm trying to incorporate a cell within an "IF" formula and the cell contains a number (2000) but its formatted as text instead of a number. This is due to the way it exports out of my system in excel. When use an =IF(A1="2000",.... its not recognizing the 2000 since its text and not a number. If I convert it to a number the formula will work but I don't want to have to convert it to a number every time.

    How can I write the formula to recognize the text?

    Thanks!

  2. #2
    Forum Contributor
    Join Date
    02-15-2010
    Location
    Everett, WA
    MS-Off Ver
    All versions; most components
    Posts
    188

    Re: Using "IF" formula on text not converted to a number

    Try removing the quotes from your "IF" statement:
    Please Login or Register  to view this content.
    That works for me.

  3. #3
    Forum Contributor
    Join Date
    02-15-2010
    Location
    Everett, WA
    MS-Off Ver
    All versions; most components
    Posts
    188

    Re: Using "IF" formula on text not converted to a number

    Also note: you can check for either using the "OR" function:

    Please Login or Register  to view this content.

  4. #4
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Using "IF" formula on text not converted to a number

    =IF(A1*1=2000,"match","no match")
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  5. #5
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: Using "IF" formula on text not converted to a number

    I am curious for this line..

    =IF(A1="2000",.... its not recognizing the 2000 since its text
    can you please attach the file.. coz.. i want to experience it..
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Using "IF" formula on text not converted to a number

    @ gyclone if you remove the "" from "2000", then it becomes a number again.

    If you do indeed have text looking like numbers, then the "2000" should work for the search. If it only works without the "", then the 2000 is a value, not text

    On a side-note, a quick way to convert "text" to values...if you look at/click on a "number" text cell, you will see a small yellow diamond. Highlight the entire range, scroll back up to the top of the range (the range should still be highlighted), click on the yellow diamond and select "convert to number"
    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

  7. #7
    Forum Contributor
    Join Date
    02-15-2010
    Location
    Everett, WA
    MS-Off Ver
    All versions; most components
    Posts
    188

    Re: Using "IF" formula on text not converted to a number

    @FDibbins: With all due respect, as you note in your comment,
    If it only works without the "", then the 2000 is a value, not text
    . Therefore, the value was already a number and removing the quotes did nothing but to allow for a match, which is what the original poster requested.

    @00Pumpkin: Ultimately, I think Ace_XL may have provided the best approach, except that, as written, it would throw an error on any cell which can't be converted to a number. If using that method, I'd wrap it in an "IF(ISERROR(". My second answer would catch either format, without throwing any errors on true text entries.

  8. #8
    Registered User
    Join Date
    08-13-2013
    Location
    Atlanta, Georgia
    MS-Off Ver
    Excel 2013
    Posts
    58

    Re: Using "IF" formula on text not converted to a number

    I was able to change the text over to a number using the VALUE function.

    Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 0
    Last Post: 08-22-2012, 03:40 PM
  2. [SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row
    By eltonlaw in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 06:26 AM
  3. [SOLVED] Re: macro for converting number stored as "text" (or preceeded with ') to "number" formatting
    By markx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-30-2006, 07:14 AM
  4. [SOLVED] macro for converting number stored as "text" (or preceeded with ') to "number" formatting
    By markx in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-23-2006, 10:20 AM
  5. [SOLVED] convert a number in Excel from numeric to text, i.e. "1" to "one"
    By buenavisionpaul in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-27-2005, 03:06 PM

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