none
Replace values in Target column by Text based on the source Column values RRS feed

  • Question

  • Am having columns Q,R,S,T of my excel spreadsheet and column R is filled with values in the cells ( 2,16,7,15 are repeated for 1000+ rows ) . i wanna change/replace these these integer values to

    "In Progress" for  2 

    and

    Canceled for 15

    and

    Approved for 16

    and

    Rejected for 17

    how to get this done? 

    tbd 4/3/2018 P4 tbd tbd tbd tbd TBD TBD tbd tbd 16    
    tbd 4/3/2018 P2                 16    
    2 4/3/2018 P4                 17    
    2 4/3/2018 P5                 16    
    2 4/3/2018 P4                 16 17  
    2 4/3/2018 P4                 16 16  
    22 4/2/2018 P3                 2    
    2 4/2/2018 P4                 16    
    2 4/2/2018 P4                 16    
    2 4/2/2018 P4                 16 16 16
    2 4/2/2018 P4                 16 16  
    2 4/2/2018 P4                 16 16  
    2 4/2/2018 P5                 16 16 16
    rfrf 4/1/2018 P4                 16    
    2 4/1/2018 P4                 16    
    2 3/30/2018 P4                 16    
    2 3/30/2018 P4                 16    
    2 3/14/2018 P3                 16    
    rfrf 3/30/2018 P3                 16 16  
    2 3/30/2018 P4                 16    
    2 3/30/2018 P4                 16    
    2 3/30/2018 P4                 16    

    as i shown above, i wanna replace the values of 16 with approved, 17 with rejected and 2 with in progress 


    • Edited by SaMolPP Tuesday, April 24, 2018 7:17 PM
    Tuesday, April 24, 2018 6:52 PM

Answers

  • Since you asked this in the Excel for Developers forum, here is a macro:

    Sub ReplaceNumbers()
        Application.ScreenUpdating = False
        With Range("R:R")
            .Replace What:=2, Replacement:="In Progress", LookAt:=xlWhole
            .Replace What:=15, Replacement:="Canceled", LookAt:=xlWhole
            .Replace What:=16, Replacement:="Approved", LookAt:=xlWhole
            .Replace What:=17, Replacement:="Rejected", LookAt:=xlWhole
        End With
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, April 24, 2018 8:24 PM