LEN Problem

[James8309]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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

 

[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)

 

[compass rose]

How about this?

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

David Rose

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