Results 1 to 4 of 4

Thread: Comparing Numbers When One Is Text Format And The Other Is Number Numeric Format

  1. #1
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13

    Comparing Numbers When One Is Text Format And The Other Is Number Numeric Format

    I have a workbook containing sveral sheets, the one being "TB consolidation" where the numbers in Col B are shown a text eg '114201 and sheet TBTXT which are genuine numbers.

    I have a formula in Sheet TBTXT in Col A which compares the account numbers in Col B to the existing account numbers in TB Consolidation and if a new number has ben added it must indicate "NEW" otherwise it must show a blank

    I cannot get the formula to work, due to the fact that the numbers in sheet TB consolidation are shown as text and the numbers in TBTXT are shown as numbers

    I have attached some sample data. It would be appreciated if you could amend the formula so that it works

    Your assistance in this regard is most appreciated
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Try this in A2=IFERROR(IF(MATCH(""&B2,'TB Consolidation'!$B$1:$B$17,0),"NEW"),"") and drag down.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    In fact, it should be A2=IF(ISERROR(MATCH(""&B2,'TB Consolidation'!$B$1:$B$17,0)),"NEW","")

    OR

    Replace the "NEW" in the previous post to "OLD"
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    Last edited by DocAElstein; 06-12-2023 at 12:59 PM.

Similar Threads

  1. Excel Number Format: Indian Style Comma Separation
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 6
    Last Post: 09-18-2013, 11:38 AM
  2. Replies: 3
    Last Post: 06-01-2013, 11:31 AM
  3. Trim Text after 3rd Underscore but retain format
    By trankim in forum Excel Help
    Replies: 4
    Last Post: 05-13-2012, 10:44 AM
  4. Replies: 4
    Last Post: 03-10-2012, 07:15 PM
  5. Convert Text In YYYYMMDD Format To Date Format
    By S M C in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 02-28-2012, 12:04 AM

Posting Permissions

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