+ Reply to Thread
Results 1 to 3 of 3

Add blank characters to end of cell so all values total 20 characters

  1. #1
    Registered User
    Join Date
    01-28-2010
    Location
    earth, milky way
    MS-Off Ver
    Excel 2010
    Posts
    47

    Add blank characters to end of cell so all values total 20 characters

    I have a column of data that isn't always the same length. I need blank spaces added to the end of the data so the total number of characters is always 20.

    Example:

    A1:ValueABC
    A2:ValueDEFGHI
    A3:ValueJ
    A4:ValueKLMNOPQRSTUVWXY

    Is there a formula that could make values in B as such:

    ValueABC____________
    ValueDEFGHI_________
    ValueJ______________
    ValueKLMNOPQRSTUVWXY

    Thanks in advance.
    Last edited by burlywood66; 11-03-2011 at 10:10 AM.

  2. #2
    Forum Expert darkyam's Avatar
    Join Date
    03-05-2008
    Location
    Houston, TX
    MS-Off Ver
    2013
    Posts
    2,191

    Re: Add blank characters to end of cell so all values total 20 characters

    Try =A1&REPT("_",20-LEN(A1))
    Life is about perseverance. Remember: today's mighty oak tree is just yesterday's nut that held its ground.

    If you like a post, please rate it with the scales icon (top right).
    If you feel really indebted, please consider a donation to charity, such as Feed the Children or Habitat for Humanity.

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Add blank characters to end of cell so all values total 20 characters

    .....slightly different version

    =LEFT(A1&REPT("_",20),20)

    If A1 has more than 20 characters that also truncates to 20
    Audere est facere

+ 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