Validating External Data

[caritx]

I know this will sound silly and I have googled and driven myself crazy because the problem seems so minor.

I get external files that sometimes have salutations:

Mr, Mr., Mrs, Mrs., Miss, Ms, Ms.,Dr, Dr.

The database I feed it to only takes: Mr., Mrs., Ms., Miss, Dr.

So I need an easy way to incorporate this into my macro:

If not one of these values (Mr., Ms., Mrs., Miss, Dr.) clear the cell.

Thank you in advance for any help you can provide.

 

[AlphaFrog]

Try something like this.

    For Each cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
        
        Select Case cell.Value
            Case "Mr.", "Mrs.", "Ms.", "Miss", "Dr."
            Case Else: cell.ClearContents
        End Select
        
    Next cell

 

[caritx]

Thank you so much that worked!

 

[AlphaFrog]

Glad it works for you.
This will add a period if the cell is Mr, Mrs, Ms, or Dr
Otherwise it clears the cell.

    For Each Cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
        
        Select Case Cell.Value
            Case "Mr.", "Mrs.", "Ms.", "Miss", "Dr."
            [COLOR="Red"]Case "Mr", "Mrs", "Ms", "Dr": Cell.Value = Cell.Value & "."[/COLOR]
            Case Else: Cell.ClearContents
        End Select
        
    Next Cell

 

[caritx]

Thank you that is awesome!