CODEWITHSUNDEEP
excelfunctionif-statementnested
user2623972
user2623972

Reputation:

Excel Nested IF Function with AND/OR

I have tied quite a few combinations of the AND/OR functions and have had no luck.

Here is what I need to create:

In column B, I have Firm ID's, which range from two numeric characters to six numeric characters.

I need every the letter "I" and a certain number of zeros before each Firm ID in column B.

-So if the Firm ID has two numbers, I need to add "I0000" to the left of the Firms ID in column B. So the end result would be "I0000##", totalling seven characters. -If the Firm ID has three numbers, I need to add "I000" to the left of the Firms ID in column B, So the end result would be "I000###", totalling seven characters. -and so on and so fourth for 4,5, and 6 character firm ID's (The Largest firm ID I have is 6 characters, so it would only need the letter "I" added)

I was trying to use something along these lines =if(len(b2=6),concatenate("I",B2),IF(len(b2=5),concatenate("I0",B2),If(Len---------you get the idea. It wasn't pretty, and it only added an "I" to all my Firm ID's.

I realize that it will require the use of either an OR( or AND( function. I tried to write it to no avail. I am a visual learner, and don't mean to troll anyone reading this, but if its not to much trouble it would really help me to see the actual function written out, so I can compare it to my incorrect function and see my mistakes.

Just pointing me to an excel training site about AND/OR functions will not help me.

Upvotes: 2

Views: 1004

Answers (2)

tigeravatar
tigeravatar

Reputation: 26650

For a formula solution, you could use the TEXT function:

=TEXT(B2,"I000000")

Alternately, you could select column B, right-click -> format cells -> Custom -> type I000000 -> OK

Upvotes: 1

Aaron Thomas
Aaron Thomas

Reputation: 5281

Doing a custom format for cells in column B should do the trick, with no coding / functions needed. Choose the cells you want to change, right click, go to format cells. From there choose the Number tab, and choose the Custom category. You could do something like "I"000000 in the box under "Type".

Upvotes: 0

Related Questions