Excel Number formatting issue

Hi

I receive extracted data from system into Excel. The issue that I want the decimal after last 2 digits.

Example :
Extracted number : 756123765
I want : 7,561,237.65

when I use (add ,00 in the ribbon)  it become : 756,123,765.00 which is wrong.
example1.xlsx
amq10Asked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
If you find yourself needing to do this kind of thing on a regular basis, here is a macro to automate the process. To use it, first select the cells to be modified, then run the macro. Blank cells and cells containing non-numeric text will be ignored.
Sub DivideBy100()
Dim n As Long
Dim ar As Range, rg As Range
Application.ScreenUpdating = False
Set rg = Selection
With Cells(Rows.Count, 1)
    .Value = 100
    .Copy
    For Each ar In rg.SpecialCells(xlCellTypeConstants)
        ar.PasteSpecial Paste:=xlPasteValues, Operation:=xlDivide, SkipBlanks:=True, Transpose:=False
    Next
    rg.NumberFormat = "#,##0.00"
    .EntireRow.Delete
    n = rg.Worksheet.UsedRange.Rows.Count
End With
End Sub

Open in new window

0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

as I understand it you want the last two digits to become the decimals of a number.  You can do this by dividing the number by 100. In your example, the number is stored as text and a formula will also result in text. So, make sure to store the number as a real number before applying formulas.

Just for fun, you could also try this formula:

=(LEFT(A1,LEN(A1)-2))+0+(RIGHT(A1,2)/100)

This formula will work with any figure showing three or more numbers.

Format the result to show thousand separators and two decimals.

cheers teylyn
0
 
Pratima PharandeCommented:
appl y formula on Cell  =A1/100

and then add formating style as Comma from Cell styles

see attchment
excel.jpg
0
 
jeff_01Commented:
Another way is to divide by 100.

=(A1/100)

You will still need to format the cell to include separators using the format code below.

#,###.00
0
 
jeff_01Commented:
Never mind me Pratima already posted this. This is why you should work on one thing at a time .

Haha
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.