Text & String Manipulation
Pad Text with Spaces or Zeros for Fixed-Width Exports (REPT + Overflow Guard)
Build exact N-character fixed-width ACH, EDI, and banking record fields by right-padding text with spaces and left-padding numeric IDs with zeros—safely clamping strings that exceed field width.
Preventing #VALUE! when input text is longer than the target width
Legacy fixed-width file specs (such as NACHA ACH banking records or warehouse EDI feeds) require every field to occupy an exact character count—for example, 12 characters right-padded with spaces for vendor names and 8 characters left-padded with zeros for invoice numbers.
Calling REPT(" ", 12 - LEN(A2)) throws #VALUE! whenever LEN(A2) exceeds 12 because REPT rejects negative counts. Truncating first with LEFT(TRIM(A2), width) and wrapping the repeat count in MAX(0, width - LEN(clipped)) guarantees an exact width-character output on every row.
| Field Type | Alignment | Exact-Width Formula Pattern |
|---|---|---|
| Alpha Name (10 chars) | Left-aligned, space-padded | LET(s, LEFT(TRIM(A2), 10), s & REPT(" ", 10 - LEN(s))) |
| Numeric ID (8 chars) | Right-aligned, zero-padded | LET(s, RIGHT(TRIM(B2), 8), REPT("0", 8 - LEN(s)) & s) |
| Full Record Line | Concatenated fixed block | Field1_10 & Field2_8 (always 18 chars) |
Overflow-Safe Fixed-Width Record Builder
Combine a 10-character space-padded vendor field with an 8-character zero-padded amount cents field so LEN(Record) is invariantly 18.
=LET(v_clip, LEFT(TRIM(A2), 10), v_pad, v_clip & REPT(" ", MAX(0, 10 - LEN(v_clip))), amt_str, TEXT(ROUND(B2 * 100, 0), "0"), amt_pad, REPT("0", MAX(0, 8 - LEN(amt_str))) & RIGHT(amt_str, 8), v_pad & amt_pad)Try the example
Use the original values or change them to test your understanding.