+ Reply to Thread
Results 1 to 7 of 7

Length of a cell with fixed characters including spaces

  1. #1
    Registered User
    Join Date
    06-01-2007
    Posts
    2

    Exclamation Length of a cell with fixed characters including spaces

    Hello All, I have a column where cells need to be 50 characters long, it contains addresses, however, since all the addresses do not meat the criteria, I need to add (blank spaces) to reach the 50 characters, any idea on how to approach this?

    Example

    Fire Street #50 this cell contains 17 characters therefore I need to add the rest by adding blank spaces to reach 50 characters, otherwise the system I am exporting the data will not understand this cell

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010
    Posts
    3,134
    Hi

    Presuming your data is in A1,

    =A1&REPT(" ",(50-LEN(A1)))

    Will increase the length to 50 characters by adding spaces to the end.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Registered User
    Join Date
    06-01-2007
    Posts
    2

    Thanks, that worked

    Thanks a lot!

  4. #4
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,677
    If by any chance the cell might contain MORE then 50 characters, this formula will add spaces to give 50 characters.....or truncate to the first 50 characters if there are more

    =LEFT(A1&REPT(" ",50),50)

  5. #5
    Registered User
    Join Date
    12-19-2013
    Location
    Boston, MA
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Length of a cell with fixed characters including spaces

    What if you want the spaces to precede the value? I tried RIGHT, but that doesn't work. Any help?

    Thank you.

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,697

    Re: Length of a cell with fixed characters including spaces

    Try

    =RIGHT(REPT(" ",50)&A1,50)

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    42,448

    Re: Length of a cell with fixed characters including spaces

    rpe94 welcome to the forum

    2 things to note...
    1. this thread almost 8 years old
    2. Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think some-1 helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1