+ Reply to Thread
Results 1 to 9 of 9

Using IF-THEN-ELSE to convert "M" to "Mr", "F" to "Ms" ELSE leave "Blank".

  1. 12-31-2012, 04:11 PM #1
    Midwicket
    Midwicket is offline
    Registered User
    Join Date
    12-31-2012
    Location
    Denver, CO, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Using IF-THEN-ELSE to convert "M" to "Mr", "F" to "Ms" ELSE leave "Blank".

    Greetings!

    I've perused about 50 pages across the Forum and haven't found anything similar to use or glean to do what I would like to accomplish. I used to know how to do this (in my youth, with Supercalc3 and Lotus 1-2-3) and drawing blanks when it comes to Excel 2007.

    I have a column with my prospects "Gender" - with answers of either "M", "F" or Blank, for Male, Female on Unknown (unverified, actually).
    I'd like to create a macro or formula to read and convert a copy of that Column to "Mr.", "Ms." or to leave blank (as is) if blank.

    I've played around with the "=IF" formula but have gotten stuck.

    I would appreciate any direction or guidance to this basic Excel formula problem. Thanks!

    Regards,

    Dan
    Reply With QuoteRegister To Reply
  2. 12-31-2012, 04:20 PM #2
    mrice
    mrice is offline
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Re: Using IF-THEN-ELSE to convert "M" to "Mr", "F" to "Ms" ELSE leave "Blank".

    =IF(A1 = "M","Mr.",IF(A1 = "F","Ms.",""))
    Martin
    Reply With QuoteRegister To Reply
  3. 12-31-2012, 04:21 PM #3
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Using IF-THEN-ELSE to convert "M" to "Mr", "F" to "Ms" ELSE leave "Blank".

    Hi Midwicket,

    Try this: 
    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
    Reply With QuoteRegister To Reply
  4. 12-31-2012, 04:21 PM #4
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Using IF-THEN-ELSE to convert "M" to "Mr", "F" to "Ms" ELSE leave "Blank".

    You could instead just select the column and replace M with Mr. and F with Ms.
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  5. 12-31-2012, 04:22 PM #5
    Midwicket
    Midwicket is offline
    Registered User
    Join Date
    12-31-2012
    Location
    Denver, CO, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Using IF-THEN-ELSE to convert "M" to "Mr", "F" to "Ms" ELSE leave "Blank".

    great - thanks for your help!
    Happy New Year!
    Reply With QuoteRegister To Reply
  6. 12-31-2012, 04:23 PM #6
    BB1972
    BB1972 is offline
    Valued Forum Contributor
    Join Date
    07-27-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2010
    Posts
    826

    Re: Using IF-THEN-ELSE to convert "M" to "Mr", "F" to "Ms" ELSE leave "Blank".

    Midwicket, welcome to the forum.

    Assuming your data is in A1, in A2 paste the following:

    Please Login or Register  to view this content.
    Hope this helps.
    Brendan.


    __________________________________________________________________________________________________
    Things to consider:

    1) You can thank any poster by clicking the * at the left of a helpful post.
    2) You can help to keep the forum tidy by marking your thread as "Solved", if it has been answered to your satisfaction.
    3) Help us to help you, by uploading a sample workbook, showing the type of data you're dealing with, and clearly indicating what the results should be.
    Reply With QuoteRegister To Reply
  7. 12-31-2012, 04:55 PM #7
    Midwicket
    Midwicket is offline
    Registered User
    Join Date
    12-31-2012
    Location
    Denver, CO, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Using IF-THEN-ELSE to convert "M" to "Mr", "F" to "Ms" ELSE leave "Blank".

    Thanks! It worked perfectly!
    Happy New Year!

    Dan
    Reply With QuoteRegister To Reply
  8. 12-31-2012, 04:56 PM #8
    Midwicket
    Midwicket is offline
    Registered User
    Join Date
    12-31-2012
    Location
    Denver, CO, USA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Using IF-THEN-ELSE to convert "M" to "Mr", "F" to "Ms" ELSE leave "Blank".

    Thanks mate!
    Happy New Year!

    Cheers!

    Dan
    Reply With QuoteRegister To Reply
  9. 12-31-2012, 05:19 PM #9
    xladept
    xladept is offline
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Using IF-THEN-ELSE to convert "M" to "Mr", "F" to "Ms" ELSE leave "Blank".

    Happy New Year!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1