need formula please

[Ron]

I have the following formala and I need B16 to always be 5
characters long. Sometimes the data in B16 is 4 or 5
digits but when that data is brought into this formula I
need it to add a space if it is only 4 characters.
Otherwise, the end result is off by one zero.


="D"&B16&" "&TEXT(D16*100,"000000000000")&TEXT
(D16*100,"000000000000")

Thanks,
Ron

 

[Bernard V Liengme]

Hi Ron
Hope I understand the question.
Replace the reference to B16 in your formula by: B16&IF(LEN(B16)=4," ","")
This will add a space after the 4 characters in B16.
Use IF(LEN(B16)=4," ","")&B16 to add before.

Bernard

 

[J.E. McGimpsey]

One way:

=LEFT("D" & B16 & " ",6) & " " & TEXT(D16*100,
"000000000000") & TEXT(D16*100, "000000000000")

or a bit more compact,

=LEFT("D" & B16 & REPT(" ", 13),18) & TEXT(D16*100,
"000000000000") & TEXT(D16*100, "000000000000")

 

[Adrian]

Try these:

for text: =IF(LEN(A2)<5,A2&"0",A2)

for value: =IF(LEN(A2)<5,A2*10,A2) or =VALUE(IF(LEN(A2)<5,A2&"0",A2))

HTH.