Results 1 to 5 of 5

Thread: Number formating
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2014
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Number formating

    I have a numeric cell, that I would like to be able to enter "-123" and the cell displays "-1.23" or if the user enters "-1.23" the cell will display "-1.23"

    is this even possible?

    Thanks

  2. #2
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Number formating

    I think only with VBA.

    Do you want to do for one particular cell or a range?
    Do you want to do only for -123 and -1.23 or for any negative number?
    What about positive numbers?

  3. #3
    Board Regular
    Join Date
    Apr 2014
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Number formating

    The cells will be in a range by columns, the numbers will be either negative or positive
    I also feel that it can only be done via VBA

  4. #4
    Board Regular
    Join Date
    Sep 2016
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Number formating

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range, cel As Range
    Set rng = Range("A:A") 'Change as required
    Set rng = Intersect(rng, Target)
    If Not rng Is Nothing Then
        rng.NumberFormat = "#,##0.00;-#,##0.00"
        Application.EnableEvents = False
        For Each cel In rng
            If IsNumeric(cel) Then If cel = Int(cel) Then cel = cel / 100
        Next
        Application.EnableEvents = True
    End If
    End Sub
    Last edited by footoo; Jul 17th, 2019 at 06:08 AM.

  5. #5
    Board Regular
    Join Date
    Apr 2014
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Number formating

    Great, is was sort of leaning that way.
    great job

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •