formatting text in a cell
 

hi all,

i need to change a value in a cell by removing a dot

for example, i need to convert 2.01 to 201

i can't find the correct formula to achieve my goal. i tried replace and
substitute but it ain't working.

can someone help?

thanks!

formatting text in a cell
 

Do you mean you want to change the value within the cell? Or that you want a
formula in another cell that removes the dot? If the latter, try this
formula...

=--SUBSTITUTE(A26,".","")

To do the former will require VB code. If the former is what you want, then
when do you want it to happen... automatically when the number is entered
into the cell or do you want a macro in order to manually do multiple cells
all at once.

--
Rick (MVP - Excel)


"Kardock" wrote in message
...
hi all,

i need to change a value in a cell by removing a dot

for example, i need to convert 2.01 to 201

i can't find the correct formula to achieve my goal. i tried replace and
substitute but it ain't working.

can someone help?

thanks!

formatting text in a cell
 

=VALUE(SUBSTITUTE(A1,".",""))

or with a UDF

Function RemDecimal(DecimalValue As Range)
Dim dbl As Double

On Error GoTo errH

dbl = DecimalValue

RemDecimal = Val(Replace(CStr(dbl), ".", ""))
Exit Function

errH:
RemDecimal = CVErr(xlErrValue)
End Function

Regards,
Peter T

"Kardock" wrote in message
...
hi all,

i need to change a value in a cell by removing a dot

for example, i need to convert 2.01 to 201

i can't find the correct formula to achieve my goal. i tried replace and
substitute but it ain't working.

can someone help?

thanks!

formatting text in a cell
 

Thanks Rick!

"Rick Rothstein" wrote:

Do you mean you want to change the value within the cell? Or that you want a
formula in another cell that removes the dot? If the latter, try this
formula...

=--SUBSTITUTE(A26,".","")

To do the former will require VB code. If the former is what you want, then
when do you want it to happen... automatically when the number is entered
into the cell or do you want a macro in order to manually do multiple cells
all at once.

--
Rick (MVP - Excel)


"Kardock" wrote in message
...
hi all,

i need to change a value in a cell by removing a dot

for example, i need to convert 2.01 to 201

i can't find the correct formula to achieve my goal. i tried replace and
substitute but it ain't working.

can someone help?

thanks!

formatting text in a cell
 

If you want to permanently change the values and overwrite the
existing values, select the cells to change and run the following
code:

Sub AAA()
Dim R As Range
Application.EnableEvents = False
For Each R In Selection.Cells
With R
If .HasFormula = False Then
If IsNumeric(.Value) Then
.Value = CDbl(Replace(CStr(.Value), _
".", vbNullString))
End If
End If
End With
Next R
Application.EnableEvents = True
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Fri, 4 Sep 2009 06:52:01 -0700, Kardock
wrote:

hi all,

i need to change a value in a cell by removing a dot

for example, i need to convert 2.01 to 201

i can't find the correct formula to achieve my goal. i tried replace and
substitute but it ain't working.

can someone help?

thanks!