+ Reply to Thread
Results 1 to 8 of 8

Based on text in cell, put number value in seperate cell

  1. #1
    Registered User
    Join Date
    05-30-2013
    Location
    Lafayette, LA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Based on text in cell, put number value in seperate cell

    Good afternoon guys,

    I have another one I have been fighting for a while. I am trying have Excel place a number value in a cell based on the text value of a different cell.

    For example:

    I have 4 text values: Overdue, Due, OK & Completed (These are all in drop down's in 1 column)
    I would like to have a 1, 2, 3 or 4 placed in a seperate column (based on priority for filtering purposes)

    I have looked in quite a few placed and havent really come up with anything, tried a few but I dont think I got anywhere close with it.
    Anyone have any thoughts?

    Thanks!

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

    Re: Based on text in cell, put number value in seperate cell

    Try

    =MATCH(A1,{"Overdue","Due","OK","Completed"},0)

    And if the cell could be blank or any other value, you can add IFERROR to make it return blank instead of an error
    =IFERROR(MATCH(A1,{"Overdue","Due","OK","Completed"},0),"")

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Based on text in cell, put number value in seperate cell

    Hi Tailor,

    Find the attached with a VLookup Table to add a Priority column that you can sort on... Look at the formulas and the table to see how it works. I've also added a Validation to Col A.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Valued Forum Contributor
    Join Date
    10-26-2008
    Location
    Birmingham, UK
    MS-Off Ver
    All versions up to 2010
    Posts
    1,025

    Re: Based on text in cell, put number value in seperate cell

    Hi
    Try this formula, you can change the cell values to suit your needs:

    =IF(A1="Overdue",1,IF(A1="Due",2,IF(A1="OK",3,IF(A1="Completed",4,""))))

    Good luck.
    Tony

  5. #5
    Registered User
    Join Date
    05-30-2013
    Location
    Lafayette, LA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Based on text in cell, put number value in seperate cell

    Outstanding, dont know how you all figure these out, seems so simple yet so darn difficult!

    Thanks again!

  6. #6
    Registered User
    Join Date
    05-30-2013
    Location
    Lafayette, LA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Based on text in cell, put number value in seperate cell

    This would also work well! I will keep this one in mind for future reference as well!

    Thanks again man!

    Can only give rep to 1 individual on 1 post though, this is equally as helpful!

  7. #7
    Registered User
    Join Date
    05-30-2013
    Location
    Lafayette, LA
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Based on text in cell, put number value in seperate cell

    Quote Originally Posted by ARGK View Post
    Hi
    Try this formula, you can change the cell values to suit your needs:

    =IF(A1="Overdue",1,IF(A1="Due",2,IF(A1="OK",3,IF(A1="Completed",4,""))))

    Good luck.
    Tony
    I tried something similar to this, but the embedded IF statements mess me up everytime, I am also sure this would work just fine, again I dont know how you all keep this stuff straight but I really do appreciate everyone's help!

    Thanks to all once again.

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

    Re: Based on text in cell, put number value in seperate cell

    Gald to help, 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