Forum Discussion
Formatting frustration in excel
I have a code column that contains values like: 250.00 1.280923E+14 452.20 All I want is to the convert the scientific notation values to their full number. I have tried so many different m...
If you set the horizontal alignment of the column to General, are the values (in particular those using scientific notation) left-aligned or right-aligned?
If they are left-aligned, they are already text values. You can convert them to numbers, but you won't be able to retrieve the extra digits that were not displayed.
If they are right-aligned, prefixing the values with ' should work...
- It looks like they are right aligned. However, prefixing them with an apostrophe 1 by 1 would be nearly impossible since there are thousands of rows with the scientific notation. Is there any way to do this automatically?
Select the range that you want to convert.
Run the macro listed below.
Blank cells and cells with formulas will be ignored.
Sub AddApostrophe() Dim cel As Range Application.ScreenUpdating = False For Each cel In Selection If Not cel.HasFormula Then If cel.Value <> "" Then cel.Value = "'" & cel.Value End If End If Next cel Application.ScreenUpdating = True End Sub- This seems to be really close to what I need. The only issue I found with running this macro is that the scientific notation now appears as 1.230123091233123E+16. It seems like the only way this works is if I double click on the cell then add the apostrophe. Is there a way to add the action of double clicking on the cell first?
