Results 1 to 10 of 10
Thread: Remove the decimal point (2003)

20071223, 03:11 #1
 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

20071223, 03:40 #2
 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 FunctionJerry

20071223, 11:57 #3
 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

20071223, 12:06 #4
 Join Date
 Dec 2003
 Location
 Perth, Western Australia, Australia
 Posts
 493
 Thanks
 82
 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)

20071223, 12:30 #5
 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

20071223, 12:39 #6
 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.
CheersRegards,
Rudi

20071223, 12:50 #7
 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, fyHope 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

20071223, 14:13 #8
 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

20071223, 14:27 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20071230, 13:19 #10
 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. )