Results 1 to 11 of 11
  1. #1
    MADawson
    Guest

    Column of numbers with a trailing space

    Using Excel 2000, I have a long column of numbers with a space after each number. This column needs to be summed. I tried formatting the cells and tried a simple macro to delete the space. Neither worked. Any suggestions? Much appreciated!

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column of numbers with a trailing space

    Assuming that your data is in column A, I would enter "=0+trim(a1)" in B1 and copy it down as far as I needed to and then sum column B.

    Brooke

  3. #3
    MADawson
    Guest

    Re: Column of numbers with a trailing space

    Thank you, Brooke, but I could not get the sum function to work on the new column. Does Excel still think it is dealing with text? I also tried the Value function "=VALUE(a1)", but that returned an error. Any other suggestions would be appreciated.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column of numbers with a trailing space

    What about "=value(trim(a1))"?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column of numbers with a trailing space

    The function = value(A1) should work, in both cases the cell A1 contains text or a number. Maybe, the data type is different, which causes your problems. You can check the variable type in your cells with the function below.

    <pre>Function Rtype(r As Range) As String
    Dim VT As Integer
    VT = VarType(r.Value)
    Select Case VT
    Case 0
    Rtype = "vbEmpty"
    Case 1
    Rtype = "vbNull"
    Case 2
    Rtype = "vbInteger"
    Case 3
    Rtype = "vbLong"
    Case 4
    Rtype = "vbSingle"
    Case 5
    Rtype = "vbDouble"
    Case 6
    Rtype = "vbCurrency"
    Case 7
    Rtype = "vbDate"
    Case 8
    Rtype = "vbString"
    Case 9
    Rtype = "vbObject"
    Case 10
    Rtype = "vbError"
    Case 11
    Rtype = "vbBoolean"
    Case 12
    Rtype = "vbVariant"
    Case 13
    Rtype = "vbDataObject"
    Case 14
    Rtype = "vbDecimal"
    Case 17
    Rtype = "vbByte"
    Case 36
    Rtype = "vbUserDefinedType"
    Case 8192
    Rtype = "vbArray"
    End Select
    End Function

    </pre>



    Knowing the variable type, may ring a bell and lead to the cause of the problem.

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

    Re: Column of numbers with a trailing space

    Select any unused cell on the worksheet and enter a 1 into that cell. Copy that cell using Copy from the Edit Menu. Select all of the cells that contain the numbers with a space after them and then select Paste Special from the Edit Menu. In the dialog box, select "Multiply" in the "Operation" section and then click the OK button. This should convert the text entries to numbers.
    Legare Coleman

  7. #7
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column of numbers with a trailing space

    I've been using the "0+" trick for converting stuff for years and it's never let me down until today. I should have known better. It failed at work AND it failed here within six hours of each other. I must be missing something. If anyone has an idea what, feel free to tell me. Meanwhile I'm storing that code for frequent use.

    Brooke

  8. #8
    MADawson
    Guest

    Re: Column of numbers with a trailing space

    Thank you all for your help, but the best solution came from my co-worker, Tom Gust:
    =LEFT(A1,LEN(A1)-1)
    I owe him an adult beverage for this.

  9. #9
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column of numbers with a trailing space

    Possibly a slightly dangerous solution, because it assumes that every number has exactly one trailing sopace. The other solutions don't assume this, so the data is safer.

    If it worked for you this time, great. Just treat it with caution.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column of numbers with a trailing space

    Mary did you have problems with =value(trim(A1))? I cannot see how that could fail.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column of numbers with a trailing space

    Kind of late on this post, but I would think that choosing Data>Text to Columns and then space delimited would have fixed this one, too.

Posting Permissions

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