Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find formatting in text in cell, insert tags around formatting. | Excel Programming | |||
Conditional formatting Based on cell A text with conditions in Cell B | Excel Discussion (Misc queries) | |||
Conditional Formatting based on text within a cell w/ text AND num | Excel Worksheet Functions | |||
Formatting Cell to text | Excel Discussion (Misc queries) | |||
Deleting Rows based on text in cell & formatting cell based on text in column beside it | Excel Programming |