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...
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?
Try this:
Sub AddApostrophe() Dim cel As Range Application.ScreenUpdating = False For Each cel In Selection If Not cel.HasFormula Then If cel.Value <> "" Then cel.NumberFormat = "0.00" cel.Value = "'" & cel.Text End If End If Next cel Application.ScreenUpdating = True End Sub