Odtnhj

I am trying to convert text of the month to the number

B2 cell:

BirthMonth_Jan

BirthMonth_Feb

BirthMonth_mar

BirthMonth_Apr

BirthMonth_May

BirthMonth_Jun, ect to december

for example, BirthMonth_Jan will output 1 based on the search of Jan, so i can compare this to another set of numbers

I have this, and tried this, but only works with two if statements, is there anyway i can do this with 12?

=(IF(ISNUMBER(SEARCH("sep",B2)),"9")),(IF(ISNUMBER(SEARCH("aug",B2)),"8")),(IF(ISNUMBER(SEARCH("jul",B2)),"7")),(IF(ISNUMBER(SEARCH("jun",B2)),"6")),(IF(ISNUMBER(SEARCH("may",B2)),"5")),(IF(ISNUMBER(SEARCH("apr",B2)),"4")),(IF(ISNUMBER(SEARCH("mar",B2)),"3")),(IF(ISNUMBER(SEARCH("feb",B2)),"2")),(IF(ISNUMBER(SEARCH("jan",B2)),"1"))

I get #Value!

If i try this, it also doesn't work

=IF(ISNUMBER(SEARCH("dec",B2)),"12",IF(ISNUMBER(SEARCH("nov",B2)),"11")),IF(ISNUMBER(SEARCH("DSH_KnowBe4_BirthMonth_Oc",B2)),"10"))

the second option only works with two but if i add more it throws an error

Not sure what you are trying to do with the formula but if your "BirthMonth_" text is consistent, you can use :

=MONTH(DATEVALUE("1 "&SUBSTITUTE(A12,"BirthMonth_","")&" 2018"))

Having a view of your data and expected result would help if this is not what you're after.

It is seems just possible what you might want is:

=MONTH(MID(B2,SEARCH("BirthMonth_",B2)+11,3)&0)

Returns a Number.

The questioner is trying to obtain a numeral equivalent to a partial month name extracted from a string. There are any number of examples in stackoverflow and the net generally on this theme. What is special in this case is the partial month name in the target cell, and use of the IF statement. The questioner is right to use search since it is not case-sensitive

Two formula are offered:

Formula 1
=(IF(ISNUMBER(SEARCH("sep",B2)),"9")),(IF(ISNUMBER(SEARCH("aug",B2)),"8")),(IF(ISNUMBER(SEARCH("jul",B2)),"7")),(IF(ISNUMBER(SEARCH("jun",B2)),"6")),(IF(ISNUMBER(SEARCH("may",B2)),"5")),(IF(ISNUMBER(SEARCH("apr",B2)),"4")),(IF(ISNUMBER(SEARCH("mar",B2)),"3")),(IF(ISNUMBER(SEARCH("feb",B2)),"2")),(IF(ISNUMBER(SEARCH("jan",B2)),"1"))

The questioner said "I get #Value!"

This is not a surprise because it is essentially a series of nine, self-contained, unrelated if statements, each separated by a comma. It is an invalid statement.
However, if the if statements were nested, then the formula would work. Something along these lines:
=IF(ISNUMBER(SEARCH("jan",B2)),"1",IF(ISNUMBER(SEARCH("feb",B2)),"2",IF(ISNUMBER(SEARCH("mar",B2)),"3")))

Formula 2
=IF(ISNUMBER(SEARCH("dec",B2)),"12",IF(ISNUMBER(SEARCH("nov",B2)),"11")),IF(ISNUMBER(SEARCH("DSH_KnowBe4_BirthMonth_Oc",B2)),"10"))

So close and yet so far... This statement uses the nested approach mentioned above. There is a major typo for the October search (instead of searching for "oct", the formula searches for "DSH_KnowBe4_BirthMonth_Oc") though this doesn't cause the formula to fail.

Failure is caused by two things:

1) The double bracket following "11")) in the "November" search. There should be zero brackets here.

2) The formula needs an additional closing bracket.

Two other things to note:

1) in the event of a match, the value returned is a string not an integer.

2) there's no provision to return a value in the event of a failure to match.

Working IF statement formula

The following formula, consisting of nested IF statements, works as intended by the questioner.
=IF(ISNUMBER(SEARCH("jan",B2)),"1",IF(ISNUMBER(SEARCH("feb",B2)),"2",IF(ISNUMBER(SEARCH("mar",B2)),"3",IF(ISNUMBER(SEARCH("apr",B2)),"4",IF(ISNUMBER(SEARCH("may",B2)),"5",IF(ISNUMBER(SEARCH("jun",B2)),"6",IF(ISNUMBER(SEARCH("jul",B2)),"7",IF(ISNUMBER(SEARCH("aug",B2)),"8",IF(ISNUMBER(SEARCH("sep",B2)),"9",IF(ISNUMBER(SEARCH("oct",B2)),"10",IF(ISNUMBER(SEARCH("nov",B2)),"11",IF(ISNUMBER(SEARCH("dec",B2)),"12",NA()))))))))))))

Note, the formula uses the NA() function to return #N/A if there is no match.

VLOOKUP alternative

Though the above-mentioned formula works, I find it complicated and inflexible. My preference in situations like this is VLOOKUP. My equivalent formula would be:
=VLOOKUP(RIGHT(B2,LEN(B2)-SEARCH("_",B2)),Sheet2!$A$2:$B$13,2,FALSE)

Using January as an example: BirthMonth_Jan, the formula lookup works like this:
RIGHT(B2,LEN(B2)-SEARCH("_",B2))

1) search for the underline character SEARCH("_",B2),

2) deduct the result from the total length LEN(B2)-SEARCH("_",B2) to give the number of characters to the right of the underline.

3) get all the characters to the right of the underline RIGHT(B2,LEN(B2)-SEARCH("_",B2)). This is the lookup value

4) Create a reference table on another sheet (refer screenshot); lookup this table and return column 2 (the number for that month).

5) If there is no valid result, VLOOKUP automatically returns #N/A.

The reference table on a separate sheet: