# Thread: Remove the decimal point (2003)

1. ## 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. ## 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

3. ## Re: Remove the decimal point (2003)

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

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

5. ## 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. ## 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

7. ## 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

8. ## 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.

9. ## 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. ## 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
•