Fig

D2 contains a telephone number. I need to check the length of the number in cell D2. If 10 or more digits, I need to E2 to display the last 10 adding a preceding 0, otherwise E2 should display the full contents of D2.

I tried entering this in E2

IF((LEN(D2)>=10),CONCATENATE(0,(RIGHT(D2,10),D2)))

but the ever so helpful Office Assistant says I have an error in the formula. I can't see anything obvious. Do I need to do this is two stages, i.e. extract the first 10 digits first, then do the concatenate bit?

greyarea

If I read your explanation correctly, the last D2 is supposed to be part of the IF statement, but your brackets have made it part of the CONCATENATE. I think you have an extra, unnecessary, set of brackets that is confusing things.

=IF((LEN(D2)>=10),CONCATENATE(0,RIGHT(D2,10)),D2)

Fig

Brilliant

Thanks for the help

dsmith

You can use "&" instead of concatenate to join strings. makes formula easier to read I find.

=IF((LEN(D2)>=10),"0" & RIGHT(D2,10),D2)

Deano