Concatenate string and number as number

Question

I have a text made of some cells with concatenation. One of the items of the text is a number. How to make it a full number with all the necesarry comas etc.

I have: on stt 03/06 db corr PLN 60000000 val 03/06 pending
I need: on stt 03/06 db corr PLN 60,000,000.00 val 03/06 pending

I tried with CDbl(Sheets(1).Cells(i, 5).Value) but the number is still the same. Please note that the source cell contains the number in correct format : 60,000,000.00 which is downloaded from DB2 database

Many thanks for any help how to achieve that

Answer 1

To concatenate a string and a number you just use &, then convert the result to a number with Val() function. Then if you want to format the result number you can use Format() function. Then you get a formatted string.

Example:

j = Val("10" & 10)
s = Format(j, "##,###.00")

Answer 2

I think that the use Format Function, resolve your problem -> https://msdn.microsoft.com/en-us/library/office/gg251755.aspx

Maybe format(60000000,"##,##0.00")?

Answer 3

If your cell is already formatted appropriately, you can use its Text property:

Sheets(1).Cells(i, 5).Text

Note that because this is the displayed value, you can get #### returned if the column width is too narrow for the actual value, but it's still useful where you don't necessarily know the format ahead of time.