Find and Replace decimal point for comma

[geofspa]

Hi Guys

I have 3 columns in Excel 2007 and in Text format containing text numbers in the form of 9.10. I can complete a "find and replace" operation finding "." and replacing with ",". some of these number run to three decimal places "9.153" and when the decimal point is replaced in this type number I have returned "9153" omitting the "," altogether. Why might this be and is there any way I can fix it?

Please note if the found number is 91.53 then after the replace I get 91,53

 

[Rick Rothstein]

I have 3 columns in Excel 2007 and in Text format containing text numbers in the form of 9.10. I can complete a "find and replace" operation finding "." and replacing with ",". some of these number run to three decimal places "9.153" and when the decimal point is replaced in this type number I have returned "9153" omitting the "," altogether. Why might this be and is there any way I can fix it?

Please note if the found number is 91.53 then after the replace I get 91,53

What is your locale's natural decimal point... a dot or a comma?

 

[geofspa]

Hi Rick

Normally decimal point is the full stop . but I need to change this for uploading to a comma , . All cells on this sheet are formatted as TEXT cells.

 

[Aladin Akyurek]

Hi Guys

I have 3 columns in Excel 2007 and in Text format containing text numbers in the form of 9.10. I can complete a "find and replace" operation finding "." and replacing with ",". some of these number run to three decimal places "9.153" and when the decimal point is replaced in this type number I have returned "9153" omitting the "," altogether. Why might this be and is there any way I can fix it?

Please note if the found number is 91.53 then after the replace I get 91,53

Care to post some mpre examples?

 

[Rick Rothstein]

Hi Rick

Normally decimal point is the full stop . but I need to change this for uploading to a comma , . All cells on this sheet are formatted as TEXT cells.

It looks like during the replacement part of its operation, Excel ignores the formatting of the cell the value came from so when it substitutes the comma, it sees 9,153 and recognizes that as a number with a thousand's separator, and place the numerical value (9153) back into the Text formatted cell. I cannot think of a non-VBA way to stop it from doing that. The VBA way to get around it is to select the cells with these values in them (you can select the whole column if you want as long as no cells in the selection contain dots that are not to be converted) and run this macro...

Sub ConvertDotToComma()
  Selection = Evaluate(Replace("IF(ISNUMBER(FIND(""."",@)),SUBSTITUTE(@,""."","",""),@)", "@", Selection.Address))
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (MoveStatesUpOneRowInColumnF) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. Since this appears to be a once only need for any given worksheet, you can now delete the macro's code. On the other hand, if you will need to do this within this same workbook at future dates, then if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[geofspa]

It looks like during the replacement part of its operation, Excel ignores the formatting of the cell the value came from so when it substitutes the comma, it sees 9,153 and recognizes that as a number with a thousand's separator, and place the numerical value (9153) back into the Text formatted cell. I cannot think of a non-VBA way to stop it from doing that. The VBA way to get around it is to select the cells with these values in them (you can select the whole column if you want as long as no cells in the selection contain dots that are not to be converted) and run this macro...

Sub ConvertDotToComma()
  Selection = Evaluate(Replace("IF(ISNUMBER(FIND(""."",@)),SUBSTITUTE(@,""."","",""),@)", "@", Selection.Address))
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (MoveStatesUpOneRowInColumnF) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. Since this appears to be a once only need for any given worksheet, you can now delete the macro's code. On the other hand, if you will need to do this within this same workbook at future dates, then if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Many thanks for your promt reply - I will take your advice and learn some more about macros and spreadsheets