2

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.

Improve this question
0

2 Answers 2

Reset to default
1

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for the swift response! I feel foolish now, I was clearly making this incredibly harderthan it needed to be.
0

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".

Improve this answer

1 Comment

Thanks! I have started setting the columns on my frequently used spreadsheets in this manner

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.