Adding Spaces to Text to Hack Center Align

[rjj920]

I was wondering if anyone knows a formula that would look at the length of a word and add spaces to center the word over 10 available character positions. For example, if I have a word that is 8 letters long, I need the formula to add one space before the word to have centered. A 3 letter word would need three spaces, and a 4 letter word would also need three.


|NATIONAL|
|||DOG||||
|||HELP|||

[6StringJazzer]

It's not hard to do this but it may not result in what you want. Most fonts are proportional fonts, which means every character, including space, has a different width. So you can add spaces but it may not result in the word being centered.

Why don't you just use formatting to center it, instead of adding spaces?

Please Login or Register  to view this content.

This formula addresses an odd number by truncating, as you did with your example of DOG. It should have 3.5 spaces so it uses 3.

[Andy Pope]

This will add the padding to the right as well.

=LEFT(REPT(" ",INT((10-LEN(A1))/2))&A1&REPT(" ",10),10)

Also interested to understand why centering the cell will not suffice

[6StringJazzer]

I couldn't figure out why padding to the right would be needed if the intent is to make it look centered within a 10-character width.

[rjj920]

This worked perfecty. Thank you!

[6StringJazzer]

Thanks for the rep!

If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."