View original (French)

Excel format HL / L / CL

ludognac -  
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   -
Hello,
I would like to know how to do this in Excel to achieve the following format:

I currently have 156.429 liters and I would like it to display as 15 hl 64 l 29 cl or 15 64 29...
I'm struggling despite a hundred attempts and multiple searches :-(

Best regards,
Ludognac
Configuration: Windows XP Firefox 3.0.14

3 answers

  1.
    eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
     
    Hello,

    you need to enter your numbers without the comma and in 5 or 6 digits.
    custom format: #0" hl "#0" l "#0" cl"
    56409 => 5 hl 64 l 09 cl

    eric
    1
  2. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    You need to convert your number to centiliters
    then custom format-cell-number type
    00" hl " 00" l " 00" cl"

    to convert to centiliters: we could use an event macro and apply the custom format
    example in the range E1:E25 to adapt 

    Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("E1:E25")) Is Nothing Then Exit Sub 'valid in the area E1:E25 to adapt Application.EnableEvents = False Target = Target * 1000 Application.EnableEvents = True Target.NumberFormat = "00"" hl "" 00"" l "" 00"" cl""" End Sub


    to install
    copy this macro
    right-click on the object name
    view code
    paste

    macro to be used sparingly
    hi Eric, sorry I hadn’t refreshed...
    --
    Best regards, Michel
    1
    1. eriiic Posted messages 24581 Registration date   Status Contributor Last intervention   7 281
       
      No problem, you fixed my extra # (I'll keep the first one though) :-)

      Otherwise, to multiply the area by a thousand, we can also enter 1000 in a cell, copy it, select the range, and use special paste multiplication.

      eric
      0
  3. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello Eric,
    I agree with you on the first #
    Have a good day
    --
    Best regards, Michel
    1