# 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
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Commented:
appl y formula on Cell  =A1/100

and then add formating style as Comma from Cell styles

see attchment
excel.jpg
Commented:
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
Commented:
Never mind me Pratima already posted this. This is why you should work on one thing at a time .

Haha
Mechanical EngineerCommented:
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
``````

Experts Exchange Solution brought to you by