Excel nested IF function, 3 cases

Question

I have a column A, that contains either one of the following values:

DATEnone
nonenone
noneTIME
DATETIME

I want to write a function that basically does this:

if A1 is "DATEnone" or A1 is DATETIME:
 A1 = "D"
elif A1 is "noneTIME":
 A1 = "T"
else:
 A1 = "S"

how can I do that in an excel cell function?

tried IF( condition1, value_if_true1, IF( condition2, value_if_true2, IF( condition3, value_if_true3, value_if_false3) )) but that breaks down when I TIME and DATE is in the string such as in DATETIME — Eric
– Eric, Commented Feb 19, 2014 at 9:33

user2140173 · Accepted Answer · 2014-02-19 09:35:30Z

3

Copy-Paste this code in a new module and hit F5 to run the macro.

Sub Main()
Application.ScreenUpdating = False
    Dim c As Range
    For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
        If StrComp("DATEnone", c, 1) = 0 Or StrComp("DATETIME", c, 1) = 0 Then
            c = "D"
        ElseIf StrComp("noneTime", c, 1) = 0 Then
            c = "T"
        Else
            c = "S"
        End If
    Next c
Application.ScreenUpdating = True
End Sub

This code iterates over column A and replaces the contents based on the conditions you specified.

And if you wanted just a function then stick this formula

=IF(OR(A1="DATEnone", A1="DATETIME"),"D", IF(A1="noneTime","T","S"))

Collectives™ on Stack Overflow

Excel nested IF function, 3 cases

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related