Padding a word with spaces to fill a cell

Question

Is it possible to pad a cell with spaces to ensure that it is the correct length?

Here's an example with * representing spaces.

Input    Output
-----    ------
red      red**
blue     blue*

The words are padded with spaces to ensure the cell content is 5 characters long.

What should happen when the input is e.g. `turquoise` i.e. longer than the 5-character limit? Truncate to `turqu`? Leave as is? Something else? And: WHY on Earth do you want to pad a cell with spaces in the first place? What are you trying to achieve? — Jean-François Corbett, Mar 31 '15 at 08:36
Related, but for numbers padded with zeroes: http://stackoverflow.com/questions/28943527/nested-ifs-to-pad-numbers-with-zeroes — Jean-François Corbett, Mar 31 '15 at 08:47
The input field would be separate to the output field and would truncate if too long. — Terry, Mar 31 '15 at 09:01

zx8754 · Accepted Answer · 2015-03-31T08:29:45.227

35

Try this:

=LEFT(A1&"*****",5)

We are adding lots of stars(*) then just cutting from left 5 characters.

enter image description here

edited Mar 31 '15 at 08:29

answered Mar 31 '15 at 08:22

zx8754

52,746
12
114
209

4

This method can be reversed to provide leading zeroes to special numbers destined to be exported to a database. Some part numbers, serial numbers and in particular DUNS numbers must be padded with leading zeroes to a fixed length. `=RIGHT("000000000" & A1, 9)` will produce the correct result. – Mar 31 '15 at 08:44
2

@Jeeped Yes, this is how I would pad numbers to have the same length. Another option would be `=TEXT(A1,"0000")` see http://stackoverflow.com/a/3992588/680068 – zx8754 Mar 31 '15 at 08:48

user3616725 · Answer 2 · 2015-03-31T10:00:37.027

22

As per suggested comment:

=LEFT(A1 & REPT("*",5),5)

original:

=A1 & REPT("*",5-len(A1))

main advantage being that you can pass the length and the pad character as a cell reference, and easily update

edited Mar 31 '15 at 10:00

answered Mar 31 '15 at 08:33

user3616725

3,485
1
18
27

2

@zx8754: True... then let's combine the best of both: `=LEFT(A1 & REPT("*",5),5)` – Jean-François Corbett Mar 31 '15 at 09:24
1

Nice. Will keep this one in my "arsenal" ;) – user3616725 Mar 31 '15 at 10:01

score 0 · Answer 3 · answered Mar 03 '17 at 04:12

0

I had to right pad the numbers in a cell with 0 to make it maximum 9 characters. All had uneven numbers in the cell. I used the below formula to achieve the result. For me the least characters present in the cell was 3. =LEFT(G41 & REPT("0",6),9)

answered Mar 03 '17 at 04:12

Souvick Bhattacharjee

11

1

This might be on the right track, but doesn't really answer the question. The OP shows strings padded with spaces to a length of 5, but you have padded with `0` to a length of 9. – MattClarke Mar 03 '17 at 04:22

Padding a word with spaces to fill a cell

3 Answers3

Linked