Change military grades into rank

[air force guy]

Afternoon all
Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work consistently, sometimes blank cell is civilian and formula stops working

 

[air force guy]

Here is what I have...
View attachment 24920
=IF(C2=1,"2 LT",IF(C2=2,"1 LT",IF(C2=3,"Capt",IF(C2=4,"Maj",IF(C2=5,"Lt Col",IF(C2=6,"Col",IF(C2=7,"Brig Gen",IF(C2=8,"Maj Gen",IF(C2=9,"Lt Gen",IF(C2=10,"Gen",IF(C2=31,"E1",IF(C2=32,"E2",IF(C2=33,"E3",IF(C2=34,"E4",IF(C2=35,"E5",IF(C2=36,"E6",IF(C2=37,"E7",IF(C2=38,"E8",IF(C2=39,"E9",IF(C2="","CIV"))))))))))))))))))))

And it's working now, not sure why it didn't work on my original worksheet

For some reason when I type this formula on another computer with "Excel 16" the blank cell still comes up "False", everything else works. Can I use this formula for all my data when I need it to convert to something else? How long can I make this formula?

 

[MARK858]

"Excel 16" the blank cell still comes up "False", everything else works. Can I use this formula for all my data when I need it to convert to something else? How long can I make this formula?

The blank cell comes up as CIV for me with 365, you can nest 64 IF's (although I wouldn't want to).
You will get a FALSE if there is a space in the cell.

 

[air force guy]

The blank cell comes up as CIV for me with 365, you can nest 64 IF's (although I wouldn't want to).

The blank cell comes up as CIV for me with 365, you can nest 64 IF's (although I wouldn't want to).

That is my next problem...same worksheet. I have random letters and numbers that can either be real or exercise. I can use the same formula but that is very time consuming, is there another way?

 

[air force guy]

That is my next problem...same worksheet. I have random letters and numbers that can either be real or exercise. I can use the same formula but that is very time consuming, is there another way?

That is my next problem...same worksheet. I have random letters and numbers that can either be real or exercise. I can use the same formula but that is very time consuming, is there another way?

BTW the spaces was the problem...thank you

 

[MARK858]

I have random letters and numbers that can either be real or exercise. I can use the same formula but that is very time consuming, is there another way

Can you post a sample of your data showing the above using the boards XL2BB addin please?

 

[air force guy]

Can you post a sample of your data showing the above using the boards XL2BB addin please?

Can you post a sample of your data showing the above using the boards XL2BB addin please?

I plan to use same formula, except there are like 20 of them

 

[MARK858]

Sorry but not sure what you want by your last post, please post the data with the result that you expect (also please use the boards XL2BB addin when posting data as we can't copy paste an image into Excel, click the XL2BB icon in the reply window for the download link and instructions).

 

[air force guy]

Sorry but not sure what you want by your last post, please post the data with the result that you expect (also please use the boards XL2BB addin when posting data as we can't copy paste an image into Excel, click the XL2BB icon in the reply window for the download link and instructions).

Sorry but not sure what you want by your last post, please post the data with the result that you expect (also please use the boards XL2BB addin when posting data as we can't copy paste an image into Excel, click the XL2BB icon in the reply window for the download link and instructions).

Understood (sorry). Will wait until I get to the house to post. Some of those made up plans are "Real" and some are "Exer", again using the same IF formula I posted earlier

 

[air force guy]

Understood (sorry). Will wait until I get to the house to post. Some of those made up plans are "Real" and some are "Exer", again using the same IF formula I posted earlier

Sorry, I'm having problems with downloading and installing XL2BB

 

[MARK858]

What problems are you having?