Adding Blank Spaces to a field Conditionally

[kime1974]

I need to add blanks to a field if it is not a certain length. For instance, if my field is last name and I need it to be 30 characters no matter what, I need to write a formula to add the blanks. This is how I do it in Crystal Reports:

{memberview_Dependents.lastname}+space(30-len({memberview_Dependents.lastname}))

I have given up on getting my data in perfect format through Crystal and now want to do it in Excel.

Thanks!

 

[SkipVought]

Hi,

Check out the LEFT, LEN and REPEAT funcitons.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[SkipVought]

Let me ask a question here. SPACES in a worksheet often make things a bit difficult. It is not a good practice to pad text with leading or trailing spaces.

If your objective is to be able to OUTPUT a file for a mainframe program to read, or any program expecting a fixed-width file, consider, instead, formatting the entire sheet as COURIER NEW 8. Set the Column Width to 30, or whatever the output spec. Then SaveAs a .PRN file.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[kime1974]

Skip - do you know if that has a column limit? I just tried it with a fraction of my file and it wrapped the last two columns. I am trying to use this for a program that needs fixed-width fields. Kim

 

[SkipVought]

How do you know that it wrapped the last two columns?

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}