How to retain the NumberFormat when converting numbers to a string in Excel VBA?

Question

Consider the following code:

Sub CombineNumbersToASTring()

' force Excel to show two decimals, even when there are trailing zeros
Range("A1", "B1").NumberFormat = "0.00"

' give some values to the previously mentioned cells
Cells(1, 1).Value = 0.1
Cells(1, 2).Value = 0.2

' combine the previous two values into one cell
Cells(1, 3).Value = "(" & Cells(1, 1).Value & ", " & Cells(1, 2).Value & ")"

End Sub

The result is erroneously

0.10 0.20 (0.1, 0.2)

although it should be

0.10 0.20 (0.10, 0.20)

So, how could I retain the NumberFormat when converting to a string in Excel VBA? The CStr method seemed not to work.

(Edit: I shall also add here that in my larger code the number of decimal places is changing from cell to another, so the decimal places can't be known from beforehand when writing the VBA code.)

PatricK · Accepted Answer · 2016-03-07 22:05:01Z

3

At least two ways you can achieve this.

Use the .Text property.
Cells(1,3).Value = "(" & Cells(1,1).Text & ", " & Cells(1,2).Text & ")"
Use Formula in Cell(1,3)
=CONCATENATE("(",TEXT(A1,"0.00"),", ",TEXT(B1,"0.00"),")")

answered Mar 7, 2016 at 22:05

PatricK

6,4231 gold badge23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

vlangen Over a year ago

The first solution is suitable for my needs, and I confirm that it works like a charm. Crazy how simple the solution was. A beforehand knowledge of the decimal places is not needed with this solution. Many thanks, indeed.

ELW · Accepted Answer · 2016-03-07 21:52:59Z

1

I think you need to use Format with "0.00" around your values like:

Cells(1, 3).Value = "(" & Format(Cells(1, 1).Value, "0.00") & ", " & Format(Cells(1, 2).Value, "0.00") & ")"

answered Mar 7, 2016 at 21:52

ELW

3903 silver badges21 bronze badges

1 Comment

vlangen Over a year ago

Yes, your solution works for the question that I originally wrote. I forgot to mention that the decimal place is not static in my larger code; I have now added that to my question. But, thank you very much anyways, and +1 for a working solution for the original question. (PatricK's solution above works even with changing decimal places.)

Juan Pablo · Accepted Answer · 2016-03-07 21:58:16Z

0

Try to extend Number format range to C1 Column (Cell(1,3)): Range("A1", "C1").NumberFormat = "0.00"

answered Mar 7, 2016 at 21:58

Juan Pablo

1,22310 silver badges15 bronze badges

Collectives™ on Stack Overflow

How to retain the NumberFormat when converting numbers to a string in Excel VBA?

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related