IF function not working..

[matthew1404]

Hi

I am trying to apply an IF function formula however am being prompted with an error

In cell A1:A5 i have sets of numbers:

A1: 564
A2:4542
A3: 68948
A4: 987456
A5: 8759048

I am trying to apply the following IF function to add 0's so they are 8 digits long and then add the letter x at the end e.g.

Please Login or Register  to view this content.

What i would like to happen:

B1:00000564x
B2:00004542x
B3:00068948x
B4:00987456x
B5:08759048x
if B6 has 8 digits, i want it to be left as it is

Please advise, much appreciated.

[Pete_UK]

Try it this way:

=REPT("0",8-LEN(A1))&A1&"x"

or like this:

=RIGHT("0000000"&A1&"x",9)

Hope this helps.

Pete

[martindwilson]

=text(a1,"00000000")&"x" or if you dont want x added if its already 8 digits
=text(a1,"00000000")&if(len(a1)=8,"","x")

[FDibbins]

remove all the ) after A1...
=IF(LEN(A1)=3,"00000"&A1&"x",IF(LEN(A1)=4,"0000"&A1&"x",IF(LEN(A1)=5,"000"&A1&"x",IF(LEN(A1)=6,"00"&A1&"x",IF(LEN(A1)=7,"0"&A1&"x")))))

or a much shorter...
=REPT("0",8-LEN(A1))&A1&"x"

[matthew1404]

Hi FDibbins

=REPT("0",8-LEN(A1))&A1&"x"

^^ worked for me great stuff.

Cheers

Thanks to all others for your input