Adding a period to records in columns

[aberfroman]

Thanks for help in advance, I have a sheet with data in a column that have letters and numbers (example. HGSJD423). I want to be able to change this description to HGSJD4.023. I have about 2,000 recordes, which are all different descriptions, but want to add ".0" in between the description. Thanks.

 

[gaz_chops]

Ignore me made a mistake!

 

[AlphaFrog]

Thanks for help in advance, I have a sheet with data in a column that have letters and numbers (example. HGSJD423). I want to be able to change this description to HGSJD4.023. I have about 2,000 recordes, which are all different descriptions, but want to add ".0" in between the description. Thanks.

This assumes the "descriptions" are all eight characters long.
=LEFT(A1,6) & ".0" & RIGHT(A1,2)

 

[Rick Rothstein]

Thanks for help in advance, I have a sheet with data in a column that have letters and numbers (example. HGSJD423). I want to be able to change this description to HGSJD4.023. I have about 2,000 recordes, which are all different descriptions, but want to add ".0" in between the description. Thanks.

Assuming the ".0" will always be placed so that the rightmost three digits will always be located after it, and assuming you want to change the values directly within their cells, give this macro a try (select the column you want to change and then run it)...

Sub InsertPeriod()
  Dim Addr
  Addr = Intersect(Selection, ActiveSheet.UsedRange).Address
  Range(Addr) = Evaluate(Replace("IF(@="""","""",replace(@,len(@)-1,0,"".0""))", "@", Addr))
End Sub

Alternately, if you are looking for a formula to display the changed values in another column, you can use this formula...

=REPLACE(A1,LEN(A1)-1,0,".0")



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 (InsertPeriod) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and 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. If you will not need to execute this macro again, just save the workbook as a normal (.xlsx) file and the macro will be stripped away at that time (answer "Yes" to the warning that a VB Project cannot be saved in a macro-free workbook).

 

[aberfroman]

Works great, thank you very much!