Make field Fixed lenght

[Guest]

Hi there,
I have following formula in sheet2 A1 ="H"&Sheet1!B3&Sheet1!D3

Which I get HABC TEST20060817, but is it possible to always make the customer
name field lenght 30?
HABC TEST 20060817
HXYZRAY TEST 20060817

The actual customer name might be 7 digits, 10 digits or 15. So I'm trying
to make it fixed lenght of 30 digits.
B3 is the customer name field.

Would appreciate any info anyone might have. Not sure if this can be done.

Thank you,
J

 

[Die_Another_Day]

="H" & Sheet1!B3 & LEFT(" ",30 - LEN("H" &
Sheet1!B3) - LEN(Sheet1!D3)) & Sheet1!D3

HTH

Charles

 

[Guest]

You can use the LEFT string function directly in the cell.
i.e
=LEFT("H"&Sheet1!B3&Sheet1!D3,30)

 

[Guest]

Hello Charles/Les,

thanks for the quick reply.
Charles, I changed yours to
="H" & Sheet1!B3 & LEFT(" ",30 -
LEN(Sheet1!B3)) & Sheet1!D3
Which gives me exactly what I want. Your code was giving me 21, I think
becuase You were including the H and Date. But was able to figure it out.

So thank you both of you for the help.

Have a good weekend.
J