Maintaining Constant Number of Characters in a String

[Ark68]

I have a field (column A) of text data. The value in the cells can be between 2 and 5 characters in length.

In column D, I must maintain the value in column A, however, the text string in column D must be exactly 15 characters long. The extra characters must be spaces.

eg. A1 = "CW", therefor D1 must equal "CW-------------", where "-" are spaces.
A2 = "BOOPY", therefore D2 must equal "BOOPY----------"

Sometimes there is no value at all in column A. In that case, column D equals the value in column B which is a constant 14 characters long. Again, column D must be 15 characters long.

eg A3="", B3="n000.00.00.000", therefore D3 must equal "n000.00.00.000-", where - is a space

So far I have this formula in column D, which so far only determines if A is empty, then substitutes a concatenated value of B + one space when A is empty.

d20=IF(A20="",CONCATENATE(B20,""),A20)

What it doesn't do, and for which I come herer for help, is to assign D a 15 character value based on the value in A.

Thank you in advance to anyone reading and able to help me with this.

 

[shg]

Maybe ...

=IF(LEN(A1), A1 & REPT(" ", 15-LEN(A1)), B3 & REPT(" ", 15-LEN(B3)))

 

[Scott Huish]

Is there only going to be a value in either one of the cells?

If so,

=LEFT(A1&B1&rept(" ",15),15)

 

[Ark68]

Thanks all for your replies ...

SHG, your solution worked wonderfully. Thank you!