Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Number of Characters (Excel 2002)

    Hello!
    I'm needing to chop off or add spaces to data within a cell so that it is only 40 characters long.
    I've used the following formula in cell A2
    =LEFT(A1,40)
    Some of the data is LESS than 40 characters, and some of the data is MORE than 40 characters. It has to be exactly 40 characters in that cell.
    In cell A3 I've added the following formula
    =LEN(A2)
    This counts how many characters.
    In cell A4 I have another formula
    =A2&REPT(" ",40-LEN(A2))
    This then adds spaces to those that are less than 40 characters.

    Is there an easier, more direct formula for this?
    Thanks!
    Lana

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Number of Characters (Excel 2002)

    In one formula:
    <code>
    =LEFT(A1&REPT(" ",40),40)
    </code>
    The formula tacks 40 spaces at the end of the value of A1, then takes the first 40 characters of the result.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Number of Characters (Excel 2002)

    Hello!
    I can't get the REPT part of the code below to work... when I leave the REPT part off and just have the LEFT part of the formula it works great... I obviously have something written wrong????
    Thanks!
    Lana

    Sub LengthofData()

    Dim lngRow As Long

    lngRow = 2
    Do While Range("A" & lngRow) <> ""
    Range("V" & lngRow) = Left(Range("M" & lngRow) & Rept(" ", 40), 40)

    lngRow = lngRow + 1
    Loop

    End Sub

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Number of Characters (Excel 2002)

    Try

    Application.WorksheetFunction.Rept(" ", 40), 40)


    instead of

    Rept(" ", 40), 40)
    Jerry

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Number of Characters (Excel 2002)

    Hi There I thought I would just add this refined code to help adjust your figures if you have a variable length:


    Sub LengthofData()

    Dim lngRow As Long
    Dim i As Integer

    lngRow = Range("A65536").End(xlUp).Row

    For i = lngRow To 2 Step -1
    Range("V" & i) = Left(Range("M" & i) & Application.WorksheetFunction.Rept(" ", 40), 40)

    Next i

    End Sub


    Enjoy
    Jerry

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Number of Characters (Excel 2002)

    REPT is a worksheet function. The VBA equivalent is String. Instead of REPT(" ",40) use:

    <code>
    String(40," ")
    </code>
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Number of Characters (Excel 2002)

    Thanks Legare... this worked perfect!
    Lana

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Number of Characters (Excel 2002)

    Lana

    Just a small point, I believe my answer works with the addition of code...sometimes a little recognition helps, I needn't have done it for you and a bit of politeness goes a long way...
    Jerry

  9. #9
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Number of Characters (Excel 2002)

    Hi Jerry,
    I apologize for my oversight on not giving credit to all that replied to my request for help! It was not my intention to offend anyone, nor to present myself as being ungrateful or rude... in fact these are pet peaves of mine, and I truly can understand your feelings. I am always very appreciative of any and all help I recieve... this includes the help that you provided! Please accept my apologies!
    Lana

Posting Permissions

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