Formatting frustration in excel

Question

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 methods but the following issues arise:

1. If I try =trim(columnwith#s) then the scientific notation numbers convert properly but the numbers with decimals turn into 250 and 452.2.

2. Converting the whole column to text. The scientific notation columns do not change.

3. Mass inserting an apostrophe before each number using the flash fill. This does not change the scientific notation numbers.

I cannot go and change each cell with scientific notation one by one because there are thousands of rows.

My ideal situation is that the code column is in text format but with the full numbers (no scientific notation and maintain all points before and after the decimal). Is there anyway for me to convert the scientific notation to the full number in text format while keeping all numbers with the decimals the same? Please advise.

hansvogelaar · Answer

tulaneboi&nbsp;
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 &lt;&gt; "" Then
                cel.Value = "'" &amp; cel.Value
            End If
        End If
    Next cel
    Application.ScreenUpdating = True
End Sub

hansvogelaar · Answer

tulaneboi&nbsp;
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...

tulaneboi · Answer

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?

tulaneboi · Answer

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?

hansvogelaar · Answer

tulaneboi&nbsp;
Try this:
Sub AddApostrophe()
    Dim cel As Range
    Application.ScreenUpdating = False
    For Each cel In Selection
        If Not cel.HasFormula Then
            If cel.Value &lt;&gt; "" Then
                cel.NumberFormat = "0.00"
                cel.Value = "'" &amp; cel.Text
            End If
        End If
    Next cel
    Application.ScreenUpdating = True
End Sub

Forum Discussion

Formatting frustration in excel

6 Replies

Resources