YTLE#73: Custom Number Format 00000

Hello,

I have a UserForm and one of the text boxes will be a share qty. Users want a minimum of three places behind decimal and max of 5 behind decimal. Is there a line of code that would state a min to max on formatting?

Thanks,
leeloo

I want a format that will show "35.4" as "35.4 lb/ft" and "35" as "35 lb/ft".
The trick being the decimal is not shown for the second number.

I currently use two different versions depending on the number, but I would
like this to be automaticly done in the format.

Hi all,
Using Excel 2007

My code below attempts to apply a custom number format to a range
When I run the code, I receive:
Run-time error '13':
Type mismatch

I I change the zero format from _(* " - "_) to 0 (zero) the code works
Why is this, and what can I do to get the custom format _(* " - "_) to work?

Thanks
goss

Code:

Option Explicit

Sub wor_FinalFormat()

    Dim wbBook As Workbook
    Dim wsData As Worksheet
    Dim myValue As String
    Dim lngRows As Long

    'Setup Environment
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    
    Set wbBook = ThisWorkbook
     
    With wbBook
        Set wsData = .Worksheets("Data")
    End With
    
    lngRows = wsData.Range("A65536").End(xlUp).Row

    With wsData.Range("A2:J" & lngRows).Font
        .Name = "calibri"
        .Size = 11
    End With
    
    wsData.Range("E1:J1").ColumnWidth = 13
    wsData.Range("E2:J" & lngRows).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* " - "_);"
    
    Set wbBook = Nothing
    Set wsData = Nothing
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
        
End Sub

I have a column with numbers like:

33.35
21.01
42.7
12.01
21

I need to convert them all to:

3335
2101
4270
1201
2100

What would be the easiest way to accomplish this? Can it be done with a custom number format?

Thanks!

Hi All,

New to this. Wondering if any body could help me a little.

How do i display the following.

1 - 5,000,000 (5 million) as 5 (without the decimal & zero)
2 - 5,100,000 (5 million 1 hundred thousand) as 5.1 (with the deciaml & one)

If I try

0.0,,

I do get the 5,100,000 as 5.1 but I also get the 5,000,000 as 5.0 which I do not want.

Thanks in advance

JAA149

I imported a file where one of the columns is numeric and has 17 possible digits and the decimal has to go before the 5th digit from the left. Ex: 160000000000 must be changed to 1600000.00000 (or 1,600,000.00). How can I do this?

Thank you.

Hi,

Hope you guys can help.

If I have the following info:

A1 = 5
A2 = -4
A3 = 0

How can I use Format Cells > Custom to have them display as:

A1 = 5 up
A2 = 4 dn
A3 = Level

I've been trying and trying but keep getting a message that I should use one of Excel's pre-defined formats.

Hair being pulled out!


Thanks in advance,
Teddy

Hi all,

Using Excel 2010.

I would like a customer number format with a leading "+" for positive number and a leading "-" for negative numbers.

My string works for positive numbers + 200.00 but not negative numbers.
The negatives display as (200.00). I would prefer - 200.00.
Current display string:
"+"_(* #,##0.00_);_(* (#,##0.00);"-"_(* "-"??_);_(@_)

I tried copying the format for positive numbers into the display for negative values and changed the sign, but that still displayed as (200.00)

I tried some variations on the format string such as
"+"_(* #,##0.00_);_(* (#,##0.00);"-"_*#,##0.00_;_(@_)

But I received back an error message:
Microsoft Excel cannot use the number format you typed

Any thoughts on how I can get the display I would like?