Center data in a fixed length text field
I know how to add in characters to the beginning or end of a cell to get it to a fixed length, but I would like to center the cell text with spaces around it.
An example of this would be if cell A1=Bob and I wanted to pad this cell to 10 characters with the cell text in the middle and spaces around it.
| Bob |
How can I do this?
I guess the post auto-deletes excess spaces, in the above example I have spaces on both sides of Bob. Get back to me if you have any questions, and thanks.
=if(len(a1)>=10, a1, rept(" ", (10 - len(a1))/2) & a1 & rept(" ", (11 - len(a1))/2) )
Entia non sunt multiplicanda sine necessitate
Since this Q is in the programming section I'd assumed vba..
What to do when the padding can't be equal either side of the word? Case in point is your example, 'Bob' has three letters leaving seven to be distributed evenly round it; 3+4 or 4+3?
Callable from sheet or code:
Please Login or Register to view this content.
from code:
from worksheet:Please Login or Register to view this content.
=PadOut("Bob",10)
=PadOut(F1,15)
No checks for the string being too long. What should happen then?
Last edited by p45cal; 11-23-2009 at 01:43 PM. Reason: use of Space instead of String (shg induced) and removal of dross
Or ...
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks