Hi everyone

I have different digit of numbers in column B and I am trying to use
"if" and " concatenate" statement together but it creates an error.

=IF(LEN(B3)=5,CONCATENATE("CB0010",B3),IF(LEN(B3)=4,CONCATENATE("CB00100",B3),IF(LEN(B3)=3,CONCATENATE("CB001000",B3),"")))

if I just go if(len(b3)=5,concatenate("CB0010",b3),"") then it works
fine but above formula returns 0

can anyone help?


thank you


regards,


James

It is always a good idea to explain **what** error you are getting rather
than to just say you got an error. I see nothing wrong wrong with your
formula and when I tried it out, it worked fine... I got no errors. By the
way, you do not need to use the CONCATENATE formula for such simple
concatenations, you can just use an ampersand (&) to concatenate the text.
So, your formula can be reduced to this...

=IF(LEN(B3)=5,"CB0010"&B3,IF(LEN(B3)=4,"CB00100"&B3,IF(LEN(B3)=3,"CB001000"&B3,"")))

Rick


"James8309" <(E-Mail Removed)> wrote in message
news:59637079-fa0d-499b-a17e-(E-Mail Removed)...
> Hi everyone
>
> I have different digit of numbers in column B and I am trying to use
> "if" and " concatenate" statement together but it creates an error.
>
> =IF(LEN(B3)=5,CONCATENATE("CB0010",B3),IF(LEN(B3)=4,CONCATENATE("CB00100",B3),IF(LEN(B3)=3,CONCATENATE("CB001000",B3),"")))
>
> if I just go if(len(b3)=5,concatenate("CB0010",b3),"") then it works
> fine but above formula returns 0
>
> can anyone help?
>
>
> thank you
>
>
> regards,
>
>
> James

On Wed, 9 Jul 2008 00:07:11 -0700 (PDT), James8309 <(E-Mail Removed)>
wrote:

>Hi everyone
>
>I have different digit of numbers in column B and I am trying to use
>"if" and " concatenate" statement together but it creates an error.
>
>=IF(LEN(B3)=5,CONCATENATE("CB0010",B3),IF(LEN(B3)=4,CONCATENATE("CB00100",B3),IF(LEN(B3)=3,CONCATENATE("CB001000",B3),"")))
>
>if I just go if(len(b3)=5,concatenate("CB0010",b3),"") then it works
>fine but above formula returns 0
>
>can anyone help?
>
>
>thank you
>
>
>regards,
>
>
>James

Your formula works OK for me.

What error do you get?

Is the data in B3 within the range 100-99999?

If the data in B3 will always be a number, the formula below is equivalent:

=IF(OR(B3>99999,B3<100),"",TEXT(B3,"""CB0010""00000"))
--ron

First, I don't use the =concatenate() function. It takes too long to type and
could eat up a function call in a long expression.

I use the & operator:
=concatenate("cb0010",b3)
is the same as:
="cb0010"&b3

Another posibility for both numbers and text:
=IF(OR(LEN(B3)<3,LEN(B3)>5),"","CB0010"&REPT("0",5-LEN(B3))&B3)



James8309 wrote:
>
> Hi everyone
>
> I have different digit of numbers in column B and I am trying to use
> "if" and " concatenate" statement together but it creates an error.
>
> =IF(LEN(B3)=5,CONCATENATE("CB0010",B3),IF(LEN(B3)=4,CONCATENATE("CB00100",B3),IF(LEN(B3)=3,CONCATENATE("CB001000",B3),"")))
>
> if I just go if(len(b3)=5,concatenate("CB0010",b3),"") then it works
> fine but above formula returns 0
>
> can anyone help?
>
> thank you
>
> regards,
>
> James

--

Dave Peterson

[compass rose]

How about this?

=LEFT("CB001000",11-LEN(B3))&B3

David Rose

Originally Posted by Dave Peterson

First, I don't use the =concatenate() function. It takes too long to type and
could eat up a function call in a long expression.

I use the & operator:
=concatenate("cb0010",b3)
is the same as:
="cb0010"&b3

Another posibility for both numbers and text:
=IF(OR(LEN(B3)<3,LEN(B3)>5),"","CB0010"&REPT("0",5-LEN(B3))&B3)



James8309 wrote:
>
> Hi everyone
>
> I have different digit of numbers in column B and I am trying to use
> "if" and " concatenate" statement together but it creates an error.
>
> =IF(LEN(B3)=5,CONCATENATE("CB0010",B3),IF(LEN(B3)=4,CONCATENATE("CB00100",B3),IF(LEN(B3)=3,CONCATENATE("CB001000",B3),"")))
>
> if I just go if(len(b3)=5,concatenate("CB0010",b3),"") then it works
> fine but above formula returns 0
>
> can anyone help?
>
> thank you
>
> regards,
>
> James

--

Dave Peterson