Normally, values in a spreadsheet column vary in length, which can cause formatting issues when you copy and paste into a plain text editor like Notepad. Instead of seeing neatly aligned columns, you end up with jagged, hard-to-read text.
To fix this, you can pad values with spaces in Google Sheets so they all have equal length. This ensures a clean, tabular look when pasting into Notepad or other text editors. In this tutorial, I’ll show you how to do it dynamically—no manual work, even if your data spans multiple columns.
Sample Data
Let’s say you have the following two-column data:
When you copy and paste this into Notepad without padding, it may look like:
It’s hard to scan because the values aren’t aligned.
With padding, the output would look like this:
Much cleaner and easier to read!
Step-by-Step: Pad Values with Spaces
Note: Instead of jumping straight to the final formula and then breaking it down, I’ll build it step by step. That way, you’ll better understand how each part works and fits into the final version. The formulas in each step build upon the previous one—they’re not meant to be used separately in different cells.
Step 1: Get Length of Each Cell Value
Use the LEN function to calculate the length of each string:
=ARRAYFORMULA(LEN(A1:A))This gives you a list of character counts for each row in column A.
Step 2: Find the Maximum Length in the Column
We want to pad all values to the length of the longest value:
=ARRAYFORMULA(MAX(LEN(A1:A)))Step 3: Calculate Number of Spaces to Add
Subtract the length of each value from the maximum length:
=ARRAYFORMULA(MAX(LEN(A1:A)) - LEN(A1:A))Step 4: Generate Spaces Using REPT
Use the REPT function to repeat the space character:
=ARRAYFORMULA(REPT(" ", MAX(LEN(A1:A)) - LEN(A1:A)))Step 5: Combine the Original Value with the Padding
Append the spaces to the original text:
=ARRAYFORMULA(A1:A & REPT(" ", MAX(LEN(A1:A)) - LEN(A1:A)))Put this formula in a new column (e.g., column C) to see padded values from column A.
To pad column B as well, copy the formula across.
Multi-Column Padding Tip
Place this in C1 for column A:
=ARRAYFORMULA(A1:A & REPT(" ", MAX(LEN(A1:A)) - LEN(A1:A)))Then place this in D1 for column B:
=ARRAYFORMULA(B1:B & REPT(" ", MAX(LEN(B1:B)) - LEN(B1:B)))Now copy the range C1:D and paste it into Notepad for clean alignment.
Why Pad Values with Spaces in Google Sheets?
- Keeps text aligned when pasting into Notepad or plain-text editors
- Useful for clean, tabular logging or sharing without formatting tools
- Makes plain text easier to scan and understand
Conclusion
That’s how you can pad values with spaces in Google Sheets to make them align properly when copying to Notepad. This formula is fully dynamic and works across multiple columns—just drag it to the right. Try it with your data and see how much cleaner your Notepad output looks!
