Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Dec 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove the decimal point (2003)

    Am wanting to remove the decimal point in a number i.e. 2007.001 to read 2007 001 ( replacing the decimal with a space )
    Have looked at ' Text to column ' but I need the result to be in the one cell.
    Many Thanks

  2. #2
    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: Remove the decimal point (2003)

    Hi There

    You could use this UDF:

    Function RemovePoint(intNumber) As String
    Dim decPlace As Integer
    intNumber = Trim(Str(intNumber))


    decPlace = (InStr(intNumber, "."))

    RemovePoint = Left(intNumber, decPlace - 1) & " " & Right(intNumber, Len(intNumber) - decPlace)


    End Function
    Jerry

  3. #3
    New Lounger
    Join Date
    Dec 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove the decimal point (2003)

    Jerry thanks for the reply.
    But sorry I'm not sure how to initiate this in my spreadsheet. If I have cell B13 containing 2007.001 how do I make it work? Or could it be made to do the converse along side in cell A13.

    smpuki

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: Remove the decimal point (2003)

    Have you tried find and replace - find the . and replace with a space (hit the space key)

  5. #5
    New Lounger
    Join Date
    Dec 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove the decimal point (2003)

    Dean
    That get's me somewhere, now I can continue until I get to the next hiccup . Thanks

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Remove the decimal point (2003)

    Hi smpuki,

    Jerry's UDF is a User Defined Function that you initialize by typing it into the spreadsheet like a standard function. So if cell B13 contains the value with the decimal, then in cell A13 type : = RemovePoint(B13).
    This will calculate the new number without the decimal.

    PS: As you can see, there are many ways to get the question answered, but Dean has provided the 'quickest/easiest' method for now.
    Cheers
    Regards,
    Rudi

  7. #7
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove the decimal point (2003)

    another option is to use the Replace formula. Assuming that your 2007.001 is in cell D2, further assume that Col E is blank
    and in cell E2 put this formula =REPLACE(D2,5,1," ") and fill down the column

    cheers, fy
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  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: Remove the decimal point (2003)

    Hi there

    Sorry, night time got in the way between my original post and now. As Rudi quite rightly states, I have written you a User Defined Function. To get this to work:

    1) Right Hand Mouse Click a worksheet Tab
    2) Click View Code
    This opens the Visual Basic Editor
    3) In the menu, Click Insert --> Module
    4) Copy and Paste the code I wrote into the right hand pane

    Close that window

    Say your value 2007.001 is in cell A1, type into B1 =RemovePoint(A1).

    Although Rudi and the others have mentioned the Replace function (quite rightly) there will be an issue if the decimal point is not the fifth character from the left. My UDF searches for the position of the decimal point in the string and replaces it with a space irrespective of its position in the string. However, if you can guarantee the decimal is in the same place each time, go fo the Replace function.
    Jerry

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

    Re: Remove the decimal point (2003)

    You can also use this formula:
    <code>
    =SUBSTITUTE(B13, ".", " ")
    </code>
    I've added a space after each argument for readability, but this is not required in the actual formula.
    This formula doesn't depend on the point being in a specific position.

  10. #10
    New Lounger
    Join Date
    Dec 2007
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Remove the decimal point (2003)

    To all that helped ~ I now have 3 good ways to deal with this problem.
    Many Thanks .... smpuki ( Would have replied earlier but my wireless connection went on the fritz. )

Posting Permissions

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