Don't display leading zeros when formatting

[sykes]

Hi Folks

I think this should be simple, but I've been on this project all day, and am getting tired...

I wish to format numbers into columns, and when displaying them ensure that they all line up.
I'm using a £ symbol, then wish to have leading spaces where there are no numbers, so that the £ symbol is always in the same place, as is the decimal point, and the trailing two numbers. Max leading digits will be thousands.
eg:

45.87 displays as.£&&45.87
145.87 as...........£&145.87
2,145.87 as.........£2145.87

(edit) I've had to use the ampersand symbol to indicate where the spaces should be, as the BBcode strips them out otherwise.

I've searched for hours, and tried using "$", "@", "?" but can't seem to get it to work.

Any help appreciated.........

 

[meldoc]

Hello

Try the Accounting Format.

 

[Dryver14]

Or go to currency and then custom

£ 00.00

 

[sykes]

Hi Brian Thanks for the reply

format(45.6,"Accounting") gave me:
A13/02/1900 14:24:0013/02/1900 14:24:00ou24ti24g

 

[shg]

I think the suggestion was to format the cells, rather than use the VBA Format function.

 

[sykes]

Folks

I didn't specify that it's a string I'm formatting - to write to a label, or text file, not a value in a cell.

Sorry for that!

 

[shg]

Sub x()
    Dim s As String
    Dim v As Variant
 
    For Each v In Array(45.87, 145.87, 2145.87)
        s = Space(10)
        RSet s = Format(v, "0.00")
        Mid(s, 1) = "$"
        Debug.Print s
    Next v
End Sub

 

[sykes]

Lovely job!
Thanks shg