Benches Uncategorized

7

Is there any way to "pad" numbers | Official Apple Support Communities

apple Verified

Imported from the Apple Support Forums via the Wayback Machine 🔗

That is, I have a series of 6 digit numbers like this, that are padded with zeros from the left

001234
003456
034567

but when I paste them into Numbers, they look like this

1234
3456
34567

This is a real problem because there are a LOT of these and I can't convert to text and then edit/correct them one by one. In Excel, there's a custom format that allows you to indicate that there should be 6 (or however many) digits, and which will automatically pad (from the left) to meet that need. I can't seem to find this (or any other workaround) in Numbers.

7 replies

Sign in or join Beachball Help to reply.

  1. 0

    KOENIG Yvan

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Welcome to the club.

    It would be fine to search in existing threads before opening a new one.

    This question was asked and responded MANY times.

    I uses some tricks

    replace the standard zeroes by slashed ones.
    As numbers neading to keep their leading zeroes are in fact strings, it is not a problem Ø622Ø for instance is my zip code
    Ø493654321 is a kind of phone number used in France.

    For phone numbers, you may also use 04 93 65 43 21. With the embedde spaces, it's no longer a number but a string which keeps its leading zero.

    When I know that those which will read my documents are reluctant to slashedZeroes, I use a nonBreakable space at the end of the number (we get it with alt + space). With it, the number will be treated as a string.

    Yvan KOENIG (from FRANCE mardi 18 mars 2008 17:30:04)

  2. 0

    degraham

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    I did search the forums. Unfortunately searching on "pad" and "numbers" comes up with virtually every reference in the forum. "Pad" alone comes up with a lot of stuff about the number pad... 8(

    If you can suggest a set of better search terms, I would be most grateful.

    Also, your (otherwise excellent) solution will not work for me - I am pulling up data from a database and have no control over the numbers' spacing or format of the zeros. I need a more direct solution, unfortunately.

  3. 0

    KOENIG Yvan

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    degraham wrote:
    I did search the forums. Unfortunately searching on "pad" and "numbers" comes up with virtually every reference in the forum. "Pad" alone comes up with a lot of stuff about the number pad... 8(

    If you can suggest a set of better search terms, I would be most grateful.

    Also, your (otherwise excellent) solution will not work for me - I am pulling up data from a database and have no control over the numbers' spacing or format of the zeros. I need a more direct solution, unfortunately.

    I apologize but on this forum you are meeting users trying to help other users.
    We can't change the behaviour of an application.

    _Go to "Provide Numbers Feedback" in the "Numbers" menu_, describe what you wish.
    Then, cross your fingers, and wait _at least_ for iWork'09

    You may also use this script.

    (*
    Enregistrer le script en tant qu'Application ou Progiciel : padWithZeroSlashed.app
    déplacer l'application créée dans le dossier
    <VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:
    Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.

    Copier les nombres à formater dans le presse-papiers
    Avec la version étendue, vous pouvez travailler sur plusieurs colonnes.
    Placez le curseur où ça vous chante dans Numbers

    menu Scripts > padWithZeroSlashed

    Le script collera les nombres formatés selon le masque ØØØØØØØ.

    +++++++

    Save the script as an Application or an Application Bundle: padWithZeroSlashed.app

    Move the newly created application into the folder:
    <startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
    Maybe you would have to create the folder Numbers and even the folder Applications by yourself.

    Copy the numbers to format in the clipboard
    With the enhanced version you may work on several colums.
    Put the cursor where you wish in Numbers

    menu Scripts > padWithZeroSlashed

    The script will paste the numbers formatted with the mask ØØØØØØØ.

    Yvan KOENIG le 18 mars 2008
    *)
    --(SCRIPT padWithZeroSlashed.app]

    property lignesAvant : missing value
    property lignesApres : missing value
    property listeCells : missing value
    property mask : missing value
    property nbChars : 5

    on run
    set theApp to "Numbers"
    my nettoie()
    try
    set txtDatas to the clipboard as Unicode text
    on error (*
    The clipboard was empty *)
    return
    end try
    set my lignesAvant to paragraphs of txtDatas
    if my parleAnglais() is false then
    set msg to "Nombre de caractères souhaité ?"
    else
    set msg to "Pad to how many digits ?"
    end if
    tell application (path to frontmost application as string) to set len to choose from list {"4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "15"} default items {nbChars as text} with prompt msg
    if len is false then return (* click on "Cancel" *)
    set nbChars to len as integer
    repeat nbChars times
    set mask to mask & "Ø"
    end repeat

    if txtDatas contains tab then
    repeat with n in my lignesAvant
    set my listeCells to my decoupe(n, tab)
    set r to ""
    repeat with i from 1 to count of my listeCells
    if i < (count of my lignesAvant) then
    set r to r & my applyMask(item i of my listeCells) & tab
    else
    set r to r & my applyMask(item i of my listeCells)
    end if
    end repeat
    copy r to end of my lignesApres
    end repeat
    else
    repeat with n in my lignesAvant
    copy my applyMask(n) to end of my lignesApres
    end repeat
    end if
    set the clipboard to my recolle(my lignesApres, return)
    tell application theApp to activate
    tell application "System Events" to tell application process (my whichProcess(theApp)) to keystroke "v" using {command down}
    my nettoie()
    end run

    --=============

    on applyMask(nn)
    local s
    copy nn to s
    if s is not "" then
    set s to text -nbChars thru -1 of (mask & nn)
    end if
    return s
    end applyMask

    --=============

    on decoupe(t, d)
    local L
    set AppleScript's text item delimiters to d
    set L to text items of t
    set AppleScript's text item delimiters to ""
    return L
    end decoupe

    --=============

    on recolle(L, d)
    local t
    set AppleScript's text item delimiters to d
    set t to L as text
    set AppleScript's text item delimiters to ""
    return t
    end recolle

    --=============

    on nettoie()
    set my lignesAvant to {}
    set my lignesApres to {}
    set my listeCells to {}
    set mask to ""
    end nettoie

    (* =============
    An application named "NumbersAnglais.app" may be already running,
    the script has activated "Numbers.app"
    but the two apps named their process "Numbers".
    We must check the "displayed name" to identity the correct one
    *)
    on whichProcess(the_App)
    local listProcess, running, i
    tell application "System Events"
    set listProcess to (displayed name of processes)
    repeat with i from 1 to count of listProcess
    set running to 0
    if item i of listProcess is (the_App & ".app") then
    set running to i
    exit repeat
    end if
    end repeat
    end tell -- to System Events
    return running
    end whichProcess

    --==================

    on parleAnglais()
    local z
    try
    tell application "Pages" to set z to localized string "Cancel"
    on error
    set z to "Cancel"
    end try
    return (z = "Cancel")
    end parleAnglais

    --==================

    --[/SCRIPT]

    Yvan KOENIG (from FRANCE mardi 18 mars 2008 19:38:22)

  4. 0

    robinmonies

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Format the cells as Text before pasting. Numbers will maintain the leading 0's, but beware that your numbers are now text and some functions won't work with them (others might).

    A1 contains 00123
    A2 =sum(A1,1) gives 1
    A3 =A1+1 gives 124

    You can also pad numbers with something like

    A3 contains 124

    =CONCATENATE(CHOOSE(LEN(A3),"0000","000","00","0",""),A3)

    which gives 00124

    Again, this is text so beware.

    numbers

    If needed, you can put the formula in an IF function to avoid an error if more or less digits are entered than what you expected. The base formula I gave above will return an error if A3 is blank or contains 123456.

    I'd fix that with something like
    =IF(len(A3)<1,"not enough digits",if(len(A3)>5,"too many digits",CONCATENATE(CHOOSE(LEN(A3),"0000","000","00","0",""),A3))

    gluck.

  5. 0

    KOENIG Yvan

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Hello

    The problem with every tip padding with standard zeroes is that these zeroes are not permanent.
    A simple copy paste is able to drop them.

    This is why, as when padding is required the values are not used as true numbers, I think that padding with slashed zeroes or adding a nonBreakable space is a better soluce.

    Of course, every user may choose this or that but it seems that it is important to know the consequences of the choice.

    Yvan KOENIG (from FRANCE mercredi 19 mars 2008 13:55:49)

  6. 0

    Jerrold Green1

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Yvan,

    What would be the problem with simply formatting the input cell to Text? Seems too easy.

    Jerry

  7. 0

    KOENIG Yvan

    Imported from the Apple Support Forums via the Wayback Machine 🔗

    Hello

    It's true that with Numbers the problem is not so severe that it is with AppleWorks.
    With this coelacanthus, copy/paste is sufficient to strip the leading zeroes.
    In Numbers they are kept.

    But,
    try to apply the script posted yesterdays to filter columns from the bottom checkboxes.
    The leading zeroes disappear.

    And the script does only that:

    set val to the clipboard as text
    set the clipboard to val

    If you want to be able to use AppleScript to enhance Numbers (and I hope that there will be an AppleScript dictionary in Numbers v2), it seems useful to learn "good" habits at the very beginning.
    Changing them later is often painful. It's a 64 years old guy which write that. I often type RANGÉE( ) when Numbers uses LIGNE( ).

    Yvan KOENIG (from FRANCE mercredi 19 mars 2008 18:56:53)