CODEWITHSUNDEEP
excelexcel-formulatext-manipulation
ukiyo1738
ukiyo1738

Reputation: 1

How do I combine multiple IF statements using different parts of a String in Excel?

Based on a String input, I need to output the meaning of the String based on different criteria's. The String length will always be 8 Characters and the first four and last two characters mean something The first four characters are always numbers with the following meaning

7885 - aplha

7886 - bravo

7887 - charlie

7888 - delta

7889 - echo

The last four characters will contain two digits and two letters. The two letters can be any letters but the two digits will always be 1. Based on the position of the two digits the output should display "a" or "b".

11ZX should output "a"

BD11 should output "b"

So if the string entered in the cell is -

788511ZX the corresponding output should be - "alpha a"

7885ZX11 the corresponding output should be - "alpha b"

7888ZX11 the corresponding output should be - "delta b"

I would ideally like this all done in one formula in a single cell

I tried inputting a formula for a single case, which returns the correct output but I have no clue how to combine all possibilities

Example input - 788511ZX entered in cell W34

Formula -

=IF(INT(LEFT(W34,4)) = 7885, "alpha") & IF(RIGHT(W34,2)="00", " a", " b")

Output - "alpha a"

If the input is changed to 7885ZX11 - the output changes to "alpha b" which is as expected. But I am struggling to combine all possibilities to include for bravo, charlie, delta and echo

Upvotes: 0

Views: 63

Answers (1)

JNevill
JNevill

Reputation: 50009

You can use the SWITCH() formula to avoid a big nested IF():

=SWITCH(LEFT(A1,4), "7885","alpha", "7886", "bravo", "7887", "charlie", "7888", "delta", "7889", "echo") & IF(ISNUMBER(RIGHT(A1, 2)), " b", " a")

You might also consider having a separate table that holds the 4-digit number to alpha-code relationship and then just use a vlookup, instead of hardcoding the relationship into a formula, too.

Upvotes: 3

Related Questions