CODEWITHSUNDEEP
excelvbaformatnumber-formatting
Gera
Gera

Reputation: 31

VBA EXCEL - NUMBERFORMAT didn´t work but FORMAT FUNCTION did

I have a range of cells named MyCells as string type. In which there are strings with 26 digits.

Why the first statement (below), with NumberFormat didn´t work, but the second did?

Trying cells look like this:

38935094000163001000052234 --> 38.935.094/0001-63 001 000052234

First attempt (didn´t work):

range(MyCells).Select

Selection.NumberFormat = "00"".""000"".""000""/""0000""-""00""   ""000""   ""000000000"

Second (successful):

range(MyCells).Select

For Each c In Selection
    c = Format(c, "00"".""000"".""000""/""0000""-""00""   ""000""   ""000000000")
Next

Upvotes: 2

Views: 12239

Answers (2)

tigeravatar
tigeravatar

Reputation: 26640

Excel only keeps the first 15 digits of numbers. That number you put up is 26 digits and the only way Excel would keep the numbers after the first 15 digits would be if it was stored as a text. As a result, number formats won't work on it because Excel sees it as a text string, not a number.

Upvotes: 0

pnuts
pnuts

Reputation: 59450

"The Format function uses different format code strings than do the NumberFormat and NumberFormatLocal properties." from http://msdn.microsoft.com/en-us/library/office/aa224873%28v=office.11%29.aspx.

Upvotes: 1

Related Questions